Friday 27 June 2014

nth Highest Salary in SQL

 
CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
--nth Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)
 
 --3rd Highest Salary
 
select e.sal from emp e 
where 2=(select count(distinct sal) from emp 
where sal>e.sal);