Loading ...

Find Nth/3rd/2nd highest salary in SQL | CodeAsp.Net

Find Nth highest salary in SQL

4.88 
 /5
4.88 (2votes)

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…

Comments (5)

   
Umesh
Umesh
This Query is very helpful for someone who is new in data base 
8/16/2010
 · 
by
   
Satheesh
Satheesh
Thank you for this nice post. really helpful
3/10/2011
 · 
by
   
Shaitender
Nice Post !!! Most popular interview question !!! BTW did some one asked u the same  
7/15/2011
 · 
by
   
Pravin
Pravin
SELECT * FROM [user] user
WHERE 3 = (SELECT count(DISTINCT(user1.Salary))
FROM [user] user1 WHERE user.Salary
8/14/2011
 · 
by
   
primary key
Here is another way to <a href="javarevisited.blogspot.dk/2012/12/how-to-find-second-highest-or-maximum-salary-sql.html">find second highest salary in MySQL and SQ Server 2008</a>
12/24/2012
 · 
by
  • :*
  • :*
  • :
 *

Top Posts