Mysql query to find nth largest entry in a column

greatest-n-per-groupMySQL

I searched on internet to find the nth largest entry in a particular column for Mysql. I found the following query:

select salary from employee e1 
where (n-1) = (select count(distinct(salary)) 
from employee e2 where e2.salary > e1.salary);

Here employee is my table name and I want to select nth highest salary from it. The query is running fine. But I don't understand how it's actually working.

Can anybody explain it?

Best Answer

SELECT salary FROM employee ORDER BY salary DESC LIMIT 7,1;

Will find the 8th highest salary.

Closer to what you did is

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 7,1;

To explain your extremely inefficient query,...

Foreach salary in employee (in any order), test (where) to see if the subquery (( select ... )) returns (n-1). The subquery searches the same table again (that's called a "self join") to count how many different (distinct) salaries are bigger (where e2.salary > e1.salary). If there are 1000 rows in the table, it will have to do 1000 scans of 1000 rows each -- a million operations.

If you need to find who has the nth largest salary, that is more complicated. If you are not worried about dups, then it is simply:

SELECT name, ... FROM employee ORDER BY salary DESC LIMIT 7,1;

If you need the DISTINCT, the a subquery is necessary:

SELECT name, ...
    FROM ( SELECT DISTINCT salary 
              FROM employee ORDER BY salary DESC LIMIT 7,1
         ) AS e2
    JOIN employee AS e1 USING(salary);
Related Question