CODEWITHSUNDEEP
sqlsqlite
user3810193
user3810193

Reputation: 53

Select most frequent month of a year in SQLITE3?

I have a table like the following- 

year        month       frequency 
----------  ----------  ----------
2501        04          33        
2501        03          911       
2503        12          377       
2503        11          3956      
2503        10          1409      
2503        07          161       
2503        06          66        
2504        03          46

How to get the most frequent month of each year to produce 

year        month       frequency 
----------  ----------  ----------      
2501        03          911            
2503        11          3956           
2504        03          46

Upvotes: 0

Views: 140

Answers (2)

CodeNewbie
CodeNewbie

Reputation: 2091

The following query provides your solution. SQLFiddle here.

select year, month, max(frequency) frequency
from mytable 
group by year

UPDATE:
Your required output had columns year, month, frequency where frequency was the maximum value of frequency per year. So max(frequency) frequency assigns the alias frequency to the query output so that it matches your requirement. Without the alias, the columns would be year, month, max(frequency). Here is the wiki on SQL aliases.

Upvotes: 1

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

Possible solution is to use join :

select t1.* 
from t t1 join (select year, max(freq) freq from t group by year) t2
on t1.year = t2.year and t1.freq = t2.freq

SQLFiddle

Upvotes: 1

Related Questions