How to find Nth highest and lowest salary in SQL

This is the one of the most common question asked in SQL. Once I was in an interview, the same question was asked to me.

I knew the answer so I wrote the query to find the Nth highest salary in a SQL table. I used the top keyword to write the SQL query.

 

But after that interviewer asked me, "Could you please write the query without using TOP keyword?". I did not know the answer.

After interview got finished, I searched for the answer and I found some interesting articles.

So here I am posting the method to find out the Nth highest and lowest salary in a SQL table with both, using TOP keyword and without using it, methods.

So let's start.

Here I have a SQL table which is having employees names and their salaries.

 

 

 

To find out the Nth highest salary (for example: here I am finding 3rd highest salary), I wrote the query like below 

SELECT TOP 1 Salary AS 'Higest Salary',Name FROM (SELECT DISTINCT TOP 3 Salary,Name FROM tblSalary ORDER BY Salary DESC) a ORDER BY Salary ASC

and the result is :



To find out the Nth lowest salary (For Example: here I am finding 3rd lowest salary) I wrote the query like below

SELECT TOP 1 Salary AS 'Lowest Salary',Name FROM (SELECT DISTINCT TOP 3 salary,Name FROM tblSalary ORDER BY Salary ASC) a ORDER BY Salary DESC

Result:

Now I founded the query for the same but without using TOP keyword.

SELECT Name, Salary FROM tblSalary a1 WHERE N-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary < OR > a1.Salary)

You just need to replace "N" with the "Number", like below I need to find out the 3rd highest and lowest salary, so I replaced N with 3.

 

To find 3rd highest salary 

SELECT Name, Salary FROM tblSalary a1 WHERE 3-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary > a1.Salary)

Result:



To find 3rd lowest salary 

SELECT Name, Salary FROM tblSalary a1 WHERE 3-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary < a1.Salary)

Result:



I hope this would be helpful.