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

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

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

returns (like user2247323's example):

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

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

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

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.

SELECT DISTINCT"F1"."food","F1"."country"FROM "foo" "F1"WHERE"F1"."food" =    (SELECT "food" FROM        (            SELECT "food", COUNT(*) AS "count"            FROM "foo" "F2"             WHERE "F2"."country" = "F1"."country"             GROUP BY "F2"."food"             ORDER BY "count" DESC        ) AS "F5"        LIMIT 1    )

Well, I wrote this in a hurry and didn't check it really well. The sub-select might be pretty slow, but this is shortest and most simple SQL statement that I could think of. I'll probably tell more when I'm less drunk.

PS: Oh well, "foo" is the name of my table, "food" contains the name of the food and "country" the name of the country. Sample output:

   food    |  country   -----------+------------ Bratwurst | Germany Fisch     | Frankreich