SQL Query to find Nth Highest or Lowest salary of an employee table

Introduction

In this post I will explain how to find Nth Highest or Lowest salary of Employee.

Description

Here i will explain how to find Nth Highest or Lowest salary of Employee in Asp.Net.

In many interviews, frequently asking question is how to find Nth Highest or Lowest salary of Employee. Many ways are there to achieve this. Here i will explain some types.

Before that, First create a table EmployeeWage with the following columns. EmployeeID, EmployeeName, EmployeeSalary.

For finding Nth Highest salary

First way to find Nth Highest salary

Declare @N int
set @N=1 --- here you can give any range of highest salary.

SELECT TOP 1 EmployeeSalary
FROM (
SELECT DISTINCT TOP @N EmployeeSalary
FROM EmployeeWage
ORDER BY EmployeeSalary DESC) ES
ORDER BY EmployeeSalary

Second way to Find Nth Highest Salary

Declare @N int
set @N=1 --- here you can give any range of highest salary.

SELECT MAX(EmployeeSalary) as 'EmployeeSalary' from EmployeeWage
where EmployeeSalary NOT IN
(
SELECT TOP @N-1 (EmployeeSalary) from EmployeeWage 
ORDER BY EmployeeSalary Desc
)

For finding Nth Lowest salary

Declare @N int
set @N=1 --- here you can give any range of highest salary. 

SELECT TOP 1 EmployeeSalary
FROM (
SELECT DISTINCT TOP @N EmployeeSalary
FROM EmployeeWage
ORDER BY EmployeeSalary ASC) ES
ORDER BY EmployeeSalary

Conclusion
In this post i tried to explain how to find Nth Highest or Lowest salary of Employee. I hope this post will helpful for you. Comment if you have any queries.