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?

2 Answers 2

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 employee ORDER BY salary DESC LIMIT 7,1
         ) AS e2
    JOIN employee AS e1 USING(salary);
  • Beware that the "LIMIT offset,number" approach does have performance implications on large offsets, i.e. the bigger offset is the more rows have to be scanned (and possibly JOINed) to get the right ones. This can hinder performance of complex queries severely.
    – redguy
    Commented Apr 10, 2015 at 12:57

What you really want is:

SELECT NTH_VALUE(salary, 8) 
       OVER (ORDER BY salary DESC) 
FROM employee

But, you will have to wait for MySQL 8 to get window functions... :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.