Mysql query to find nth largest entry in a column 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 &gt; 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?
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 &gt; 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);