How to find Nth Highest salary in SQL server ?

In this post I wrote sql qureis to find Highest Salary, Second Highest, Third Highest ,Lowest salary and Nth Highest salary from EmpMaster table.

First I will create a database table in sql:-

CREATE TABLE [dbo].[tblEmpMaster](

       [EmpID] [int] NOT NULL,

       [EmpName] [varchar](100) NOT NULL,

       [EmpSalary] [int] NULL,

       [Age] [int] NULL

     )

Let me Insert some records in above table:-

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (1, N'Ram', 50000, 28)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (2, N'Naveen', 22000, 25)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (3, N'Neetu Singh', 17000, 27)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (5, N'R. P Singh', 62000, 23)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (6, N'Mohit', 27000, 24)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (6, N'Amit', 15000, 26)

GO

Now Records in Above table are:-

Query :-

SELECT [EmpID]

      ,[EmpName]

      ,[EmpSalary]

      ,[Age]

  FROM [Employee].[dbo].[tblEmpMaster]

Result:-

1. Highest EmpSalary using Aggregate Max() function

Select Max(EmpSalary) from [dbo].[tblEmpMaster]

2. Second Highest EmpSalary using Aggregate Max() function

Select Max(EmpSalary) from [dbo].[tblEmpMaster] where EmpSalary not in(Select Max(EmpSalary) from [dbo].[tblEmpMaster])

3.Highest EmpSalary using TOP  Clause

Select TOP 1 EmpSalary from [dbo].[tblEmpMaster] order by EmpSalary desc

4. Third Highest EmpSalary using TOP  Clause

Select TOP 1 EmpSalary from(Select TOP 3 EmpSalary from [dbo].[tblEmpMaster]

 order by EmpSalary desc)t order by EmpSalary asc

5. Third Lowest EmpSalary using TOP  Clause

Select TOP 1 EmpSalary from(Select TOP 3 EmpSalary from [dbo].[tblEmpMaster]

 order by EmpSalary asc)t order by EmpSalary desc

6. Now finally here I wrote a sql query to find Nth salary

Select * from [dbo].[tblEmpMaster] t1 where N-1=(Select Count(Distinct(EmpSalary))

 from [dbo].[tblEmpMaster] t2 where t2.EmpSalary>t1.EmpSalary)

Suppose If I will pass N=3 the the above query become like

Select * from [dbo].[tblEmpMaster] t1 where 2=(Select Count(Distinct(EmpSalary))

 from [dbo].[tblEmpMaster] t2 where t2.EmpSalary>t1.EmpSalary)

And it will find the third Highest salary from the table see result below

In the above article I try to explain the Mostly asked sql queries In interviews with example. I hope its useful to you. Please post your feedback, question, or comments about this article.