Finding the nth highest salary of an employee.

Finding the nth highest salary of an

employee.

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

--Highest Salary
select max(Emp_Sal) from Employee_Test

--3rd Highest Salary
select min(Emp_Sal) from Employee_Test where
Emp_Sal in
(select distinct top 3
 Emp_Sal from Employee_Test order by Emp_Sal desc)

--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)