You are currently viewing How To Find Nth Highest Salary in SQL Server

How To Find Nth Highest Salary in SQL Server

How To Find Nth Highest Salary in SQL Server

Finding the Nth highest salary is a common interview question in SQL Server interview.

First we will create a table tblEmployee and insert some rows, then we find the Nth highest salary with some practical examples.

 –Create a table tblEmployee
CREATE TABLE tblEmployee
(
          EmpID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
          EmpFirstName NVARCHAR(20) NOT NULL,
          EmpLastName NVARCHAR(20) NULL,
          EmpAge INT NULL,
          EmpGender NVARCHAR(10) NULL,
          EmpSalary INT NULL
)
GO

–Insert rows into tblEmployee table.

INSERT INTO tblEmployee
(
    EmpFirstName, EmpLastName, EmpAge, EmpGender, EmpSalary
)
VALUES
    ('john','dave',26,'male',26000),
    ('kamran','akmal',35,'male',25000),
    ('umar','akmal',36,'male',38000),
    ('simran','sidhu',25,'female',33000),
    ('diyana','jeni',26,'female',50000),
    ('karishma','rai',27,'female',70000),
    ('anam','chaudhary',29,'female',25000),
    ('Amelia','sidhu',56,'female',50000)
GO

We could see the result of tblEmployee in the following image.

Get 2nd Highest Salary Using CTE

WITH CTEResult AS
(
         SELECT EmpID, EmpFirstName, EmpLastName, EmpSalary, DENSE_RANK() OVER(ORDER BY EmpSalary DESC) AS DENSERANK 
          FROM tblEmployee
)
SELECT TOP 1 EmpID, EmpFirstName, EmpLastName,EmpSalary
FROM CTEResult WHERE CTEResult.DENSERANK=2

We can see the 2nd highest salary using CTE in the following image.

Get 3rd Highest Salary Using TOP Keyword

SELECT TOP 1 EmpSalary FROM
(
         SELECT DISTINCT TOP 3 EmpSalary FROM tblEmployee
         ORDER BY EmpSalary DESC ) Result
         ORDER BY EmpSalary ASC

We can see the 3rd highest salary using TOP in the following image.

Get 4th Highest Salary Using Inner Query

To find the 4th Highest salary using inner query we will use the following formula. 

SELECT * FROM tblEmployee e1
 WHERE N-1=(SELECT COUNT(DISTINCT EmpSalary) FROM tblEmployee e2   WHERE e2.EmpSalary>e1.EmpSalary)

In the above syntax, N is the highest salary which you want to find, We use alias e1 for outer query and alias e2 for inner query to differentiate between outer and inner query table.

SELECT * FROM tblEmployee e1
WHERE 4-1=(SELECT COUNT(DISTINCT EmpSalary) FROM tblEmployee e2   WHERE e2.EmpSalary>e1.EmpSalary) 

We can see the 4th highest salary using Inner Query in the following image.

Recommended Readings

Buy Best Stationery Products in Pakistan

Piano Ballpoint Pen

Piano Ballpoint Pen

ORO Trimetal Pencil

ORO Trimetal

Dollar Clipper Pen

UHU 10×7 ml Pack of 10 PCs

UHU 10×7 ml Pack of 10 PCs

Glue Stick 8g Pack Of 12 PCs

Glue Stick 8g

Color Pencils Box of 12 PCs

Deer Multi Color Pencils

Leave a Reply