One student of me asked "how can we get nth highest and lowest salary on an employee ?". In this article I am going to expose, how can we achieve this in SQL Server.
Suppose we have employee name and salary as shown in below fig.

Query to get nth(3rd) Highest Salary
- Select TOP 1 Salary as '3rd Highest Salary'
- from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC)
- a ORDER BY Salary ASC

Query to get nth(3rd) Lowest Salary
- Select TOP 1 Salary as '3rd Lowest Salary'
- from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC)
- a ORDER BY Salary DESC

No comments:
Post a Comment