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
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL