N'th Highest salary in Employee Table

Hi Folks ,
What is the most common question in any of SQL development/DB Testing Interview.

write a query for
2'nd highest salary in employee table.

Generally we use a nested query approach for this question as per below

select employee_sal,employee_name 

from employee where employee_sal=(select max(employee_sal) 

from employee where employee_sal<(select max(employee_sal) 

from employee))

Now if I ask for 3rd highest salary , then again we have to add one more level of subquery...cool...?
but if i ask for 12th highest salary then hmm????
So obviously this is not the right way to wrtite this type of query .
below is generic for Nth highest salary.
lets see how
2nd higest salary

select employee_sal,employee_name from employee emp1 
where 1=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

3rd Highest salary

select employee_sal,employee_name from employee emp1 
where 2=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

I think now you have got the idea...now
Nth highest salary

select employee_sal,employee_name from employee emp1 
where (N-1)=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

Hope this helped

 Please subscribe for the Blog , if this helped you