Tuesday, 15 January 2013

Find nth Highest salary(value) from table

Presume: Let there is a table name Employee and it has a field name salary(bigint).
Problem: I want to get 5th(nth) highest salary.
Solution:Here is the query to retrieve 5th highest salary from employee table

                      SELECT TOP 1 Salary FROM Employee WHERE Salary IN
                              (SELECT TOP 5 DISTINCT( Salary) FROM Employee ORDER BY  Salary DESC )
                                ORDER BY Salary ASC

Note: Above query can be generalized to retrieve nth highest or lowest record from any table.