Tag Archives: Row_Number()

Nth Highest salary & Nth Lowest salary of employee

There are two approaches for the same, I will show the example with N=2
1 using CTE

Highest
With cteH
{
   Select Emp_Name, Emp_Salary, Row_Number() over (Order By Emp_Salary Desc) as Rn
   From Emp
}

Select Emp_Name, Emp_Salary
From cteH
Where Rn=2

Lowest
With cteL
{
   Select Emp_Name, Emp_Salary, Row_Number() over (Order By Emp_Salary Asec) as Rn
   From Emp
}
Select Emp_Name, Emp_Salary
From cteL
Where Rn=2

2 using Distinct

Highest
Select e1.Emp_Name, e1.Emp_Salary
From Emp e1
Where 2=(Select Count(DISTINCT(e2.Emp_Salary)) From Emp e2
  Where e2.Emp_Salary>=e1.Emp_Salary)

Lowest
Select e1.Emp_Name, e1.Emp_Salary
From Emp e1
Where 2=(Select Count(DISTINCT(e2.Emp_Salary)) From Emp e2
  Where e2.Emp_Salary<=e1.Emp_Salary)

Leave a comment

Filed under SQL