Find Nth/3rd/2nd highest salary in SQL

This is most popular question for the interview.
Here I will discuss how to find nth highest salary. Consider we have a User table the column name of the user table are UserName, Salary, City ,Suppose inthis table we have following data

UserName Salary    City
Vijendra         5000    Delhi
Vijay              7200    Noida
Vimlesh         4000    Agra   
Vikas             2700    Lucknow   
Vimal             3700    Noida

Now we find the 3rd highest salary from this table :

SELECT MIN(SALARY) AS Salary FROM [user] WHERE SALARY IN (SELECT DISTINCT TOP 3 SALARY FROM [user] ORDER BY SALARY DESC)

3rd highest salary without MIN function:

SELECT Top 1 Salary AS Salary FROM [user] WHERE SALARY IN (SELECT DISTINCT TOP 3 SALARY FROM [user] ORDER BY SALARY DESC) ORDER BY SALARY

We also find the 3rd highest salary as like:

SELECT * FROM [user] user
WHERE 3 = (SELECT count(DISTINCT(user1.Salary))
FROM [user] user1 WHERE user.Salary<= user1.Salary)

To find the Nth highest salary we use the following is comman query:

SELECT * FROM [user] user
WHERE N= (SELECT count(DISTINCT(user1.Salary))
FROM [user] user1 WHERE user.Salary<= user1.Salary)

Hope it will help to all…