Find Nth highest salary - SQL database

In this article i will explain how to find the 1st,2nd, 3rd and nth highest salary from the SQL server.

Step 1 : Create a new database in SQL server or if you have a database already go to step 2.

CREATE DATABASE (YOUR DATABASE NAME);

Step 2 : Create a table in the database.

CREATE TABLE Employee
(
    EmployeeID INT,
    Name Varchar(250),
    Salary Decimal (10,2)

);

Step 3 : Insert some sample data to the table.

INSERT INTO Employee VALUES (1,'Scott',1000);
INSERT INTO Employee VALUES (2,'John',1100);
INSERT INTO Employee VALUES (3,'Phil',1200);
INSERT INTO Employee VALUES (4,'Peter',1300);
INSERT INTO Employee VALUES (5,'Jose',1400);

INSERT INTO Employee VALUES (6,'Morgan',1500);

Step 4 : Now lets see how to write a query to get the 1st,2nd, 3rd or nth highest salary.

1st highest salary

Select Max(Salary) from Employee where Salary NOT IN ( Select TOP (2-1) Salary from Employee ORDER BY Salary DESC)

Result : 1500

2nd highest salary

Select Max(Salary) from Employee where Salary NOT IN ( Select TOP (3-1) Salary from Employee ORDER BY Salary DESC)

Result : 1400

Nth highest salary

Select Max(Salary) from Employee where Salary NOT IN ( Select TOP (n-1) Salary from Employee ORDER BY Salary DESC)

where n will be replaced by the positive integer (1,2,3,4....n) to get the 1st or 2nd, 3rd or
4th  and nth salary.


In other cases we need to get the details of the employees like (EmployeeID,Name,Salary) along with the salary then we can use the following query.

Select * from Employee e1 where (n-1) = (select Count(distinct(Salary)) from Employee e2 where e2.Salary > e1.Salary )

where n will be replaced by the positive integer (1,2,3,4....n) to get the 1st or 2nd, 3rd or
4th  and nth salary.



Happy Coding..!!!