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
![SQL SERVER]()
Query to find the highest salary
SELECT*FROM [DBO].[EMPLOYEE] ORDER BY SALARY DESC
SELECT MAX(SALARY) FROM EMPLOYEE
![SQL SERVER]()
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)
![SQL SERVER]()
SELECT * FROM EMPLOYEE ORDER BY SALARY DESC
SELECT DISTINCT TOP 2 SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC
![SQL SERVER]()
Nth number of highest salary using DENSE_RANK function
SELECT SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SALARY_RANK
FROM EMPLOYEE
![SQL SERVER]()
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
![SQL SERVER]()