Get most common value for each value of another column in SQL

Question

I have a table like this:

 Column  | Type | Modifiers 
---------+------+-----------
 country | text | 
 food_id | int  | 
 eaten   | date | 

And for each country, I want to get the food that is eaten most often. The best I can think of (I'm using postgres) is:

CREATE TEMP TABLE counts AS 
   SELECT country, food_id, count(*) as count FROM munch GROUP BY country, food_id;

CREATE TEMP TABLE max_counts AS 
   SELECT country, max(count) as max_count FROM counts GROUP BY country;

SELECT country, max(food_id) FROM counts 
   WHERE (country, count) IN (SELECT * from max_counts) GROUP BY country;

In that last statement, the GROUP BY and max() are needed to break ties, where two different foods have the same count.

This seems like a lot of work for something conceptually simple. Is there a more straight forward way to do it?

Answer 1

PostgreSQL introduced support for window functions in 8.4, the year after this question was asked. It's worth noting that it might be solved today as follows:

SELECT country, food_id
  FROM (SELECT country, food_id, ROW_NUMBER() OVER (PARTITION BY country ORDER BY freq DESC) AS rn
          FROM (  SELECT country, food_id, COUNT('x') AS freq
                    FROM country_foods
                GROUP BY 1, 2) food_freq) ranked_food_req
 WHERE rn = 1;

The above will break ties. If you don't want to break ties, you could use DENSE_RANK() instead.