How to find nth highest salary using sql server

To find nth highest salary of employee using sql server

We have many ways to find nth highest salary of employee from database table

1. To find nth highest salary using Sub-Query

For Example:- We have EmployeeSalary table in database having id,name,department and salary details

Below screenshot shows the design of table

nth highest salary


Query:- We manually entered some data into table for the example


nth highest salary

Query to find nth highest salary. Here we find 3rd highest salary of employee

nth highest salary


select top 1 * from 
(select distinct top 3 salary,name,department from EmployeeSalary order by salary desc)result
order by salary


2. To find nth highest salary using CTE(Common Table Expressions)

nth highest salary



This query finds the 2nd hightest salary of the employee. Change where denserank condition according to your requirement

WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS denserank
    FROM EmployeeSalary
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE denserank = 2


To run this query on your sql server please copy below code:- 

GO
/****** Object:  Table [dbo].[EmployeeSalary]    Script Date: 3/6/2020 6:13:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeSalary](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[department] [nvarchar](50) NOT NULL,
[salary] [int] NOT NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[EmployeeSalary] ON

INSERT [dbo].[EmployeeSalary] ([id], [Name], [department], [salary]) VALUES (1, N'Deepshikha', N'IT', 13400)
INSERT [dbo].[EmployeeSalary] ([id], [Name], [department], [salary]) VALUES (2, N'Empreet', N'CSE', 30000)
INSERT [dbo].[EmployeeSalary] ([id], [Name], [department], [salary]) VALUES (3, N'Roohnoor', N'Civil', 28000)
INSERT [dbo].[EmployeeSalary] ([id], [Name], [department], [salary]) VALUES (4, N'Daljit Singh', N'IT', 43000)
INSERT [dbo].[EmployeeSalary] ([id], [Name], [department], [salary]) VALUES (5, N'Mandeep', N'ECE', 25000)
INSERT [dbo].[EmployeeSalary] ([id], [Name], [department], [salary]) VALUES (6, N'Gagandeep', N'ECE', 18500)
INSERT [dbo].[EmployeeSalary] ([id], [Name], [department], [salary]) VALUES (7, N'Deep', N'ME', 45000)
SET IDENTITY_INSERT [dbo].[EmployeeSalary] OFF

Two methods to find nth highest salary are as:-

1. To find nth highest salary using Sub-Query:-
select top 1 * from 
(select distinct top 3 salary,name,department from EmployeeSalary order by salary desc)result
order by salary

2.  To find nth highest salary using CTE(Common Table Expressions)
WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS denserank
    FROM EmployeeSalary
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE denserank = 2






Comments :

Post a Comment