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

It is now even simpler: PostgreSQL 9.4 introduced the mode() function:

select mode() within group (order by food_id) from munch group by country 

returns (like user2247323's example):

country | mode -------------- GB      | 3 US      | 1 

See documentation here: https://wiki.postgresql.org/wiki/Aggregate_Mode

https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE