How to find Nth highest and lowest salary in SQL

This is the one of the most common question is 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 key word?". 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.

Thanks