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)

Advertisements

Leave a comment

Filed under SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s