Create subqueries for each value in a Column

I’m looking to create a SQL query that answers the following question: “Which was the most common destination airport in each month?” The table is structured such that it has Month and Destination_Airport columns (amongst other things), and I’m hoping for the query result to look like the following:

Month	Destination_Airport
1	JFK
2	LAX
3	BOS
4	MIA
and	so on

I’ve tried this query SELECT Month, Destination_Airport FROM Flight GROUP BY Month, Destination_Airport ORDER BY COUNT(*) DESC which gets me all of the Month, Destination_Airport pairs ordered by their frequency, but I don’t know how to trim it down to only return the most popular airport per month, and nothing more. Thanks in advance for any help!

Answer

You are on the right track. You can then use window functions to get the top per month:

SELECT md.*
FROM (SELECT Month, Destination_Airport, COUNT(*) as cnt,
             ROW_NUMBER() OVER (PARTITION BY Month ORDER BY COUNT(*) DESC) as seqnum
      FROM Flight
      GROUP BY Month, Destination_Airport 
     ) md
WHERE seqnum = 1;