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
Second 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
For finding Nth Lowest 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
)
Conclusion
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
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.