SQL get the most frequent item for each object-CodePudding

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)

CodePudding user response：

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.