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

Solution 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

Solution 2:

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.

Solution 3:

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