This is 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 the interview was 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 will be helpful.