sql query to find nth highest salary from salary table

Question

How can i find the Nth highest salary in the sql server

Answer 1

Modify table names and fields as appropriate, but this should work:

SELECT * FROM employees ORDER BY emp_salary ASC LIMIT n,1

where n = nth highest salary

---

This doesn't work with MS SQL, but you can use this to fetch, say, the top ten salaries:

SELECT TOP 10 * FROM employees ORDER BY emp_salary ASC

And then you could iterate through the results until you got the nth salary that you wanted.

Answer 2

Try

SELECT TOP 1 Salary
FROM 
(
    SELECT TOP N Salary
    FROM Salaries
    ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC

where N is defined by you.

SalarySubquery is the alias I have given to the subquery, or the query that is in parentheses.

What the subquery does is it selects the top N salaries (we'll say 3 in this case), and orders them by the greatest salary. So let's say you have the following salaries in the table Salaries:

 EmployeeID  Salary
--------------------
     10101  50,000
     90140  35,000
     90151  72,000
     18010  39,000
     92389  80,000

If we want to see the third-highest salary, the subquery would return:

 Salary
-----------
80,000
72,000
50,000

The outer query then selects the first salary from the subquery, except we're sorting it ascending this time, which sorts from smallest to largest, so 50,000 would be the first record sorted ascending.

As you can see, 50,000 is indeed the third-highest salary in the example.

Answer 3

You could use row_number to pick a specific row. For example, the 42nd highest salary:

select  *
from    (
        select  row_number() over (order by Salary desc) as rn
        ,       *
        from    YourTable
        ) as Subquery
where   rn = 42

Windowed functions like row_number can only appear in select or order by clauses. The workaround is placing the row_number in a subquery.