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(*).

Answer 1

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).

Answer 2

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.