Find Nth Highest Salary using Dense_rank Function in SQL

Pramod Thakur
9y
19.3k
0
1

Blog

Let’s create a table and name it table1.

CREATE TABLE [table1](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL,
[Salary] [int] NULL,
[DepId] [int] NULL
)

Let’s insert some record in table.

SET IDENTITY_INSERT [table1] ON
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (1, N'abc', 1231, 1)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (2, N'def', 1256, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (3, N'ghi', 5631, 3)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (4, N'jkl', 4654, 4)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (5, N'mno', 5645, 5)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (6, N'pqr', 1261, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (7, N'stu', 1931, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (8, N'test1', 4256, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (9, N'test2', 9341, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (10, N'test3', 5043, 3)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (11, N'test4', 715, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (12, N'test5', 7895, 5)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (13, N'test6', 1515, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (14, N'test7', 2785, 5)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (15, N'test8', 3140, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (16, N'test9', 4694, 3)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (17, N'test10', 638, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (18, N'test11', 8256, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (19, N'test12', 2772, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (20, N'test13', 8986, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (21, N'test14', 4980, 1)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (22, N'test15', 5800, 1)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (23, N'test16', 6977, 4)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (24, N'test17', 4602, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (25, N'test18', 9720, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (26, N'test19', 9523, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (27, N'test20', 8694, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (28, N'test21', 1994, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (29, N'test22', 1940, 3)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (30, N'test23', 2621, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (31, N'test24', 3651, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (32, N'test25', 3208, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (33, N'test26', 6497, 5)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (34, N'test27', 9152, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (35, N'test28', 4551, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (36, N'test29', 4809, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (37, N'test30', 3014, 4)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (38, N'test31', 309, 1)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (39, N'test32', 6295, 1)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (40, N'test33', 5389, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (41, N'test34', 9229, 3)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (42, N'test35', 4610, 3)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (43, N'test36', 8066, 5)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (44, N'test37', 1789, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (45, N'test38', 8318, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (46, N'test39', 7518, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (47, N'test40', 1556, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (48, N'test41', 4284, 4)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (49, N'test42', 1679, 6)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (50, N'test43', 1829, 5)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (51, N'test44', 7685, 7)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (52, N'test45', 9523, 5)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (53, N'test46', 7349, 2)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (54, N'test47', 597, 1)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (55, N'test48', 4111, 3)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (56, N'test49', 2633, 1)
INSERT [table1] ([id], [Name], [Salary], [DepId]) VALUES (57, N'test50', 9894, 4)
SET IDENTITY_INSERT [table1] OFF

Let’s write a query for selecting Nth salary,

With CTE AS
(
select DENSE_RANK() over(partition by depid order by salary desc) As DR,
DepId,Name,Salary from table1
)
-- In this code i am trying to fetch 2nd highest salary. If you want to select 5th highest salary then pass DR=5
Select DepId,Name,Salary from CTE where DR=2