SQL get the most frequent item for each object

Question

Table: Food
+------+------+
|Person| Food |
+------+------+
|  A   | Apple|
|  A   | Bread|
|  A   | Bread|
|  A   | Bread|
|  B   |Orange|
|  B   |Orange|
|  B   |Orange|
|  C   |Orange|
|  C   |Orange|
+------+------+

How do you find the the most frequent food a person ate and how many times he ate it. The output would be something like

A - Bread - 3
B - Orange - 3
C - Orange - 2

I have tried this. But right now the count just gives the amount of food he ate and not the most frequent one.

SELECT PERSON,FOOD, COUNT(FOOD)
FROM FOOD
GROUP BY FOOD
ORDER BY COUNT(FOOD)

separate rows, like `A - Bread - 3` and `A - Orange - 3`? or together like `A - Bread, Orange - 3`? — ysth, Jul 11 '22 at 22:21

brobers · Accepted Answer · 2022-07-12T00:19:44.670

Here is a working solution:

SELECT PERSON, FOOD, MAX(c) FROM  
(SELECT PERSON, FOOD, COUNT(*) AS c  
FROM FOOD  
GROUP BY PERSON, FOOD  
ORDER BY c DESC) AS v  
GROUP BY PERSON;

Here is a link to a SqlFiddle: http://sqlfiddle.com/#!9/726798/45

If you want to show the whole list of food counts per person, you can do this:

SELECT PERSON, FOOD, COUNT(*) AS C
FROM FOOD
GROUP BY PERSON, FOOD
ORDER BY PERSON, C DESC

SqlFiddle: http://sqlfiddle.com/#!9/726798/53

See here Get top n records for each group of grouped results for some more information about finding the arbitrary top n most frequent.

score 0 · Answer 2 · answered Jul 12 '22 at 00:34

You can add rank to your query and make it a subquery to select on that:

SELECT PERSON, FOOD, food_count
FROM (
    SELECT PERSON,FOOD,COUNT(FOOD) food_count, DENSE_RANK(COUNT(FOOD)) OVER (PARTITION BY PERSON) food_rank
    FROM FOOD
    GROUP BY PERSON, FOOD
) ranked_counted_foods
WHERE food_rank = 1

To select the top three food counts for a person, change to = 3. Then RANK() and DENSE_RANK() will give slightly different behaviors when there are ties. DENSE_RANK will return 2 even if it returned multiple 1s for ties; RANK will skip numbers when there are ties.

SQL get the most frequent item for each object

2 Answers2