Finding an element with max. no of appearing in SQL

Question

I have the following table:

id | year

10 | 2000

11 | 2001

10 | 2002

12 | 2003

11 | 2004

13 | 2005

10 | 2006

10 | 2007

According to id, since 10 appears most, the selection should give 10 for this table. I know this is easy but I couldn't go further than COUNT(*).

score 2 · Answer 1 · answered Jan 14 '12 at 22:04

2

You need a group by, order by - along with a limit:

SELECT id FROM sometable GROUP BY id ORDER BY COUNT(*) DESC LIMIT 1

This will group the table by id, order them in descending order by their count and pick the first row (the one with highest count).

answered Jan 14 '12 at 22:04

kastermester

3,058
6
28
44

1

since there can be more than one highest count with same count of course, picking first row may not be enough. – Gary Leather Jan 14 '12 at 23:47
Yeah I hadn't realised that was of concern based off of the question - I see someone already posted the answer for that problem - although I'm not sure that the posted query is not overly complex. – kastermester Jan 15 '12 at 00:22

dgw · Accepted Answer · 2012-01-14T22:24:19.300

The following SQL will work when there is more then one id having the maximum count:

SELECT id FROM table GROUP BY 1 
HAVING COUNT(*)=( SELECT MAX(t.count) 
                    FROM ( SELECT id,COUNT(*) AS count
                             FROM table GROUP BY 1 ) t )

The first (innermost) SELECT will just count each id, this is used in the second SELECT to determine the maximum count and this will be used in the final (outermost) SELECT to display only the right IDs.

Hope that helps.

Finding an element with max. no of appearing in SQL

2 Answers2