Tuesday, November 18, 2014

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.

1 comment:

  1. I have read your blog its very attractive and impressive. I like it your blog.

    Dot Net Training in Chennai Dot Net Training in Chennai .Net Online Training .Net Online Training Dot Net Training in Chennai Dot Net Training in Chennai


    Dot Net Online Training Dot Net Online Training LINQ Online Training LINQ Online Training ASP.NET Online Training ASP.NET Online Training

    ReplyDelete

Followers