Thursday, May 15, 2014

Get nth highest and lowest salary of an employee

http://www.dotnet-tricks.com/Tutorial/sqlserver/SQ23310812-Get-nth-highest-and-lowest-salary-of-an-employee.html

ne 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

  1. Select TOP 1 Salary as '3rd Highest Salary'
  2. from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC)
  3. a ORDER BY Salary ASC

Query to get nth(3rd) Lowest Salary

  1. Select TOP 1 Salary as '3rd Lowest Salary'
  2. from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC)
  3. a ORDER BY Salary DESC

No comments:

Post a Comment