Post
Ask Question

Find Nth Highest Salary in SQL Server

Introduction

In this article, I am explaining various ways to get the Nth highest salary in SQL Server from an EMPLOYEE table.

  1. Create a Table


    Here the EMPLOYEE table has three columns Id, Name, and SALARY. Id is a primary key which is an autogenerated column and incremented by 1 from the seed value 1. SALARY is a field where we store the salary of an employee. The following figure shows the EMPLOYEE table creation code. 
    CREATE TABLE EMPLOYEE
(
ld INT IDENTITY (1, 1) PRIMARY KEY,
Name NVARCHAR (30) ,
SALARY DECIMAL
)

  2. Insert Employee data in the EMPLOYEE table


    Now we insert a number of rows in the EMPLOYEE table using the row constructor. In this, we define each row in brackets ( ) and each row is separated by a comma. In the brackets, each field is also separated by a comma. The following figure shows that 10 rows were inserted into the EMPLOYEE table using a single line of code but in this code, we are not passing the Id field because it is an autogenerated column. 
    INSERT INTO EMPLOYEE (Name, Salary)
VALUES
('XXX', 1200),
('YYY', 1000),
('ZZZ', 1300),
('XXX', 1200),
('ABC', 1500),
('PQR', 1000),
('ABC', 1600),
('XYZ', 1800),
('ZZZ', 1300),
('XYZ', 1100);

  3. Show all employee data


    After inserting 10 rows into the EMPLOYEE table we use a select statement to show all employee data in the EMPLOYEE table. 
    SELECT * FROM EMPLOYEE;

    Select-Table-in-SQL-Server.png

  4. Get 3 Highest Salary using the TOP


    To get the 3 highest salary from the employee table we create a result set of 3 rows and after that, we get the top 1 from these 3 rows.

    First of all, create a SELECT statement that returns 3 rows. In this we get the top 3 rows using order by salary in descending order from the EMPLOYEE table;  
    SELECT TOP 3 Name, Salary
FROM Employee
ORDER BY Salary DESC;

    Now we have the above result which has 3 rows and we want the lowest salary from this. So to get the lowest salary we need to specify the ascending order for SALARY so we can get the lowest salary on top. So we give an alias name to the result set "Emp". 
    SELECT Name, Salary
FROM (
  SELECT TOP 3 Name, Salary
  FROM Employee
  ORDER BY Salary DESC
) AS Emp
ORDER BY Salary;

    Now we can get the lowest salary on top. In other words, we have the three highest salaries of the EMPLOYEE table data on top like-

    lowestsalaryontop-from-Table-in-SQL-Server.jpg

    Now we can get the topmost salary from this like- 
    SELECT TOP 1 Name, Salary
FROM (
  SELECT TOP 3 Name, Salary
  FROM Employee
  ORDER BY Salary DESC
) AS Emp
ORDER BY Salary;

    We get the result in the following figure-

    Output-from-table-in-SQL-Server.jpg

    Suppose we don't want to specify ascending order for salary obtained from the EMPLOYEE table; in that case, we can't define the TOP 1 salary so we will use a MIN to get the lowest salary from this result set which is the third highest salary in the EMPLOYEE table. 
    SELECT MIN(Salary) AS Salary
FROM (
  SELECT TOP 3 Name, Salary
  FROM Employee
  ORDER BY Salary DESC
) AS Emp;

    And in the output, we will get the third highest salary from the EMPLOYEE table.

    Result-from-table-in-SQL-Server.jpg

  5. Get 4th Highest salary using MAX


    To get the nth highest salary using MAX we need to define a SELECT statement that returns the n highest salaries from the EMPLOYEE table. 
    SELECT TOP 3 Name, Salary
FROM Employee
ORDER BY Salary DESC;

    It will return the 3 highest salaries in the Employee Table.

    Now we have the 3 highest salaries in the EMPLOYEE table.

    So now we get the maximum salary from the EMPLOYEE table which is not in the 3 highest salaries; that salary will be the 4th highest salary in the EMPLOYEE table. This code is as in the following figure.

    Top3-salary-from-Table-in-SQL-Server.jpg

    Now we have the 4th highest salary. 
    SELECT max(Salary)
FROM Employee
WHERE Salary NOT IN (
  SELECT TOP 3 Salary
  FROM Employee
  ORDER BY Salary DESC
);

  6. Get 4th Highest Salary without TOP and MAX


    Here we create two aliases of a single table and compare the first table's salaries to another table's salaries row by row.

    get-Top4-salary-without-max-in-SQL-Server.jpg

    In this, we compare the first table, each salary to the second table's each salary. We want to get the 4th highest salary so that means there will be 3 salaries greater than the 4th salary. So now we count how many salaries are greater than the second table by comparing to the first table each salary where we will get that there are 3 salaries greater than from a specific salary from the first table that the salary will be the 4th highest salary.

    SELECT Emp1.Name,Emp1.salary 
FROM Employee emp1 
WHERE 4-1 = 
(
	SELECT COUNT(DISTINCT emp2.salary) FROM employee emp2 
	WHERE emp2.salary>emp1.salary
)

Conclusion

In this article, we learned about How to Find Nth Highest Salary in SQL Server with code examples.


Similar Articles