Finding the nth highest salary of an employee SQL

Create a table named Employee_Test and insert some test data as:-

Create a table named Employee_Test and insert some test data as:-

 Collapse

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

It is very easy to find the highest salary as:-

 Collapse

--Highest Salary

select max(Emp_Sal) from Employee_Test

Now, if you are asked to find the 3rd highest salary, then the query is as:-

 Collapse

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

The result is as :- 1200 
To find the nth highest salary, replace the top 3 with top n (n being an integer 1,2,3 etc.)

 Collapse

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