Find Nth highest salary using Dense_rank function in sql

In this article we learn how to find nth highest salary using Dense_Rank function is sql.

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

Press F5 and see the output.

OutPut

1

I hope you enjoy this article.
Happy coding 🙂

Tags: ,