Find Nth Highest Salary in SQL Server

Find Nth Highest Salary in SQL Server:

In this Post we try to find the nth max salary from sql data base. Some time developer want to this type of query when they give more search options in asp.net web site.

So here we give step by step process to find , 2, 3 …….highest salary form sql table.

Find 2nd Highest Salary in SQL Server:

Write a SQL query to get the second highest salary from the table above. Also write a query to find the nth highest salary in SQL, where n can be any number.

SELECT MAX (Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX (Salary) FROM Employee)

Find the 3rd or Nth Highest Salary in a Table via SubQuery:

In that case we need to modify the above SQL Query. What we need to do is to select the maximum Salary from the Salary table where Salary is greater than maximum Salary from the Salary table. So the modified SQL Query will be like:

Select MAX (Salary) from Salary where Salary< (select MAX (Salary) from Salary)

The output will be 10000.00

So getting the second highest salary is simple enough. Now we try to Getting the Nth Highest Salary.

SQL query to find find the nth highest salary:

SELECT TOP 1 Salary FROM

(

SELECT DISTINCT TOP (2) Salary FROM Salary ORDER BY Salary DESC) Result ORDER BY Salary

But, the question was to get the nth highest salary. No issues! We are done with it. Just replace 2 in the inner query with 3 to get the 3rd highest salary, with 4 to get the 4th highest salary and so on.

Search This Blog

Tutorials Place