This is the extension of the article of finding the second highest salary. Here we’ll see how to find the N-th highest salary. It is more generic version which is applicable for second highest also.
Lets consider this employee table.
DBMS Independent Way
Here we’ll not use any DBMS specific keywords. This SQL query should work for any relational database.
SELECT emp_name, salary FROM employee as e1 WHERE (SELECT COUNT(DISTINCT salary) FROM employee as e2 WHERE e2.salary > e1.salary) = N-1
The logic is to figure out a salary for which we’ll have N-1 number of higher salaries. The inner subquery will have N-1 entries for the returned salary.
The example below figured out the 3-rd highest salary.
We used 3 in place of N to get the 3rd highest salary. Based on our example table, this query return Jack’s salary (2765) as 3rd highest salary.
MySQL Specific Way
MySQL supports pagination with keyword LIMIT. We can first sort the table in ascending order of salary and return one entry starting for (N-1).
SELECT salary FROM employee ORDER BY salary DESC LIMIT (N-1),1;
To figure out 3rd highest salary we have to use 2, not (2-1) in the above query.
SQL Server Specific Way
Here the inner query returns N rows with N highest salaries. The returned rows are sorted in ascending order of salary. The first row will have the N-th highest salary.
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM employee ORDER BY salary DESC ) AS temp ORDER BY salary
To get the 3rd highest salary, we have to use 3 in place of N.
