How To Find The Highest Salary In SQL Server

Introduction

In this blog, we will discuss how to find the highest salary, the second highest salary, and N number of the highest salaries with different examples.

Step 1

Create a table in SQL server and insert some data.

CREATE TABLE [dbo].[Employee](  
    [ID] [int] IDENTITY(1,1) NOT NULL,  
    [Name] [nvarchar](50) NULL,  
    [Position] [nvarchar](50) NULL,  
    [Office] [nvarchar](50) NULL,  
    [Salary] [int] NULL,  
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
(  
    [ID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO 

Step 2

Write a query to select all records from the table:

SELECT * FROM EMPLOYEE

 

Query to find the highest salary

SELECT*FROM  [DBO].[EMPLOYEE] ORDER BY SALARY DESC  
  
SELECT MAX(SALARY) FROM EMPLOYEE

 

Query to find second highest salary

SELECT * FROM EMPLOYEE ORDER BY SALARY DESC  
  
SELECT MAX(SALARY) FROM EMPLOYEE   
WHERE SALARY<(SELECT MAX(SALARY) FROM EMPLOYEE)  

 

SELECT * FROM EMPLOYEE ORDER BY SALARY DESC  
  
SELECT DISTINCT TOP 2 SALARY  
FROM EMPLOYEE  
ORDER BY SALARY DESC  

 

Nth number of highest salary using DENSE_RANK function

SELECT SALARY,  
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SALARY_RANK  
FROM EMPLOYEE  

 

TO FIND NTH HIGHEST SALARY USING CTE

SELECT*FROM  [DBO].[EMPLOYEE] ORDER BY SALARY DESC  
GO  
  
WITH RESULT AS  
(  
    SELECT SALARY,  
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK  
    FROM EMPLOYEE  
)  
SELECT TOP 1 SALARY  
FROM RESULT  
WHERE DENSERANK = 3