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.
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.
No comments:
Post a Comment