Search code examples
mysqlsqldatabaseselecttop-n

Finding N Largest elements in a column

I have a city table comprising of fields such as ID, Name, CountryCode and Population. I want to find the top N(say N = 5) cities with the largest Population.

A very naive way would be to find the city with the largest population using the MAX() group function, and finding the rest via a variant of the method mentioned here.

What is the simplest SQL Query to find the second largest value?

Does anyone know of a better method to achieve the goal?

Solution

  • If you just want the top N cities, using order by and limit would be a much simpler approach:

    SELECT   *
FROM     city
ORDER BY population DESC
LIMIT    5 -- or any other N