Find nth Highest Salary and nth Lowest Salary of an Employee in SQL Server

This is a very common question for interviews and exams. Following is the best way to find the nth highest and lowest salary of an employee in SQL Server.

USE [AdventureWorks]

GO

-- Create tbl_emp Table

CREATE TABLE [dbo].[tbl_emp](

       [EmpCode] [int] NOT NULL,

       [EmpName] [nvarchar](50) NOT NULL,

       [EmpSalary] [float] NOT NULL

)

-- Insert records in the table

GO

INSERT [dbo].[tbl_emp] ([EmpCode],[EmpName], [EmpSalary]) VALUES (1, N'Peter', 1000)

GO

INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary]) VALUES (2, N'Robin', 1200)

GO

INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary]) VALUES (3, N'Sam', 1000)

GO

INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary]) VALUES (4, N'Katie', 900)

GO

INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary]) VALUES (5, N'Jack', 1000)

GO

INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary]) VALUES (6, N'David', 750)

GO

INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary]) VALUES (7, N'Tina', 1100)

GO

INSERT [dbo].[tbl_emp] ([EmpCode], [EmpName], [EmpSalary]) VALUES (8, N'James', 1100)

GO

-- See records in the table

SELECT * FROM tbl_emp

-- SQL Query to find 2nd Highest Salary

SELECT EmpCode, EmpName, EmpSalary FROM

(

SELECT *, DENSE_RANK() OVER(ORDER BY EmpSalary DESC) AS DRANK

FROM tbl_emp

) AS NewTbl

WHERE NewTbl.DRANK = 2

-- SQL Query to find 2^nd Lowest Salary

SELECT EmpCode, EmpName, EmpSalary FROM

(

SELECT *, DENSE_RANK() OVER(ORDER BY EmpSalary ASC) AS DRANK

FROM tbl_emp

) AS NewTbl

WHERE NewTbl.DRANK = 2