Finding N Largest elements in a column

Question

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?

why dont order by population in descending and put limit N ? , that will give you N largest cities by population. — Ratul Sharker, Feb 13 '16 at 09:40
There is not necessarily such a thing as "the" top N cities. There could be more than N tied, and there could be fewer. PS Apparently you haven't even tried googling 'stackoverflow sql' with your question title. — philipxy, Feb 13 '16 at 09:53
Possible duplicate of [Find n largest values in a column](http://stackoverflow.com/questions/12705789/find-n-largest-values-in-a-column) — philipxy, Feb 13 '16 at 09:54

score 2 · Accepted Answer · answered Feb 13 '16 at 09:43

2

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

answered Feb 13 '16 at 09:43

Mureinik

297,002
52
306
350

Be careful if there are multiple rows with the same population. LIMIT may not give the result you expect. – nvogel Feb 13 '16 at 10:26

score 1 · Answer 2 · answered Feb 13 '16 at 10:26

SELECT *
FROM cities AS c1
WHERE
(SELECT COUNT(*)
 FROM cities AS c2
 WHERE c2.population > c1.population)<5;

This query may return more than five rows if there are several cities with the same population. You didn't specify what result you expect for "top 5" when there are tied values.

Finding N Largest elements in a column

2 Answers2