Top 5 Ways to get 3rd Highest Salary from Employee Table in SQL

There are multiple ways to get the 3rd highest Salary from an Employee Table in a Company, follow the below steps from creation of an Employee Table till executing SELECT query to get 3rd highest Salary from top, in this article we have shown top 5 ways to get third highest Salary from Employee table of Company using CTE, DENSE_RANK(), ROW_NUMBER().

Step 1: Create an Employee Table : Use the below CREATE TABLE script to create a new Employee Table:

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [EmpName] [nvarchar](200) NOT NULL,
    [EmpDesignation] [nvarchar](200) NOT NULL,
    [EmpDepartment] [nvarchar](200) NOT NULL,
    [ManagerId] [int] NULL,
    [Salary] INT
)

GO

STEP 2: Insert records into Employee Table: Use the below INSERT scripts to insert rows into an Employee Table:

INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId], [Salary]) VALUES ('William Smith', 'Project Manager', 'Software', NULL,125000)
GO                                                                                
INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId], [Salary]) VALUES ('Ketty Keen', 'Technical Lead', 'Software', 1,105000)
GO                                                                                
INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId], [Salary]) VALUES ('Ketty Keen', 'QA Lead', 'Software', 1,100000)
GO                                                                            
INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId], [Salary]) VALUES ('John Parker', 'Senior Software Developer', 'Software', 2,95000)
GO                                                                                
INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId], [Salary]) VALUES ('Ellen White', 'Trainee Software Engineer', 'Software', 2,70000)
GO                                                                                
INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId], [Salary]) VALUES ('Robert Gray', 'Software Tester', 'Software', 3,85000)
GO

STEP 3: SELECT on Employee Table: After executing above CRATE TABLE and INSERT scripts, we will be getting following Table result, when we do SELECT on Employee table:

SELECT * FROM Employee

STEP 4: Writing SQL SELECT query to get 3rd Highest Salary from Employee Table in a Company:

Method 1:

SELECT TOP 1 Salary
FROM (SELECT TOP 3 Salary FROM Employee ORDER BY Salary DESC) AS EmpSalay
ORDER BY Salary ASC

Method 2:

SELECT MAX(Salary)
FROM  Employee
WHERE  Salary NOT IN (SELECT TOP 2 Salary from Employee ORDER BY Salary DESC)

Method 3:

SELECT Salary FROM Employee emp1
WHERE 2 = (
    SELECT COUNT(DISTINCT(emp2.Salary))
    FROM Employee emp2

    WHERE emp2.Salary > emp1.Salary)

Method 4: Using CTE & ROW_NUMBER() to get 3rd Highest Salary from Employee Table:

with employee_salary_ordered as
(
SELECT Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) row_num
FROM Employee
)
select Salary
from employee_salary_ordered
where row_num = 3

Method 5:  Using DENSE_RANK() method, we can find out 3rd Highest Salary:

SELECT
emp.EmpName,emp.Salary
FROM(SELECT EmpName,Salary,DENSE_RANK() over(ORDER BY Salary DESC) AS rk FROM Employee) as emp where rk=3

Using all the above 5 methods, we will get our 3rd Highest Salary result as shown below:

You can try any of the above method to get the 3rd Highest Salary from your Employee table.