Wednesday, August 17, 2011

How to find the nth highest salary in SQL-Server?

Posted by Rahul Kharde at 11:57 PM
This is the most common question that find out the 2nd or 3rd..... Nth highest salary from the Employee table
Example


CREATE TABLE Emp
(
    eId INT IDENTITY(1,1) not null,
    eName VARCHAR(50) not null,
    Salary INT not null
)
INSERT INTO Emp(eName, Salary) VALUES('abc',20000 )
INSERT INTO Emp(eName, Salary) VALUES('pqr',60000 )
INSERT INTO Emp(eName, Salary) VALUES('xyz',40000 )
INSERT INTO Emp(eName, Salary) VALUES('mno',50000 )
INSERT INTO Emp(eName, Salary) VALUES('def',30000 )
INSERT INTO Emp(eName, Salary) VALUES('lmn',10000 )
 
There is many ways to find out the nth most highest salary.
Solution 1

SELECT TOP 1 salary FROM Emp WHERE salary not in (
                  SELECT max(salary) FROM Emp) ORDER BY salary DESC)

Solution 2
For 2nd Highest salary
SELECT * FROM Emp e1 WHERE 2=(SELECT count(*)FROM Emp e2 WHERE e1.salary<=e2.salary)
For 3rd Highest salary
SELECT * FROM Emp e1 WHERE 3=(SELECT count(*)FROM Emp e2 WHERE e1.salary<=e2.salary)
For 4rd Highest salary
SELECT * FROM Emp e1 WHERE 4=(SELECT count(*)FROM Emp e2 WHERE e1.salary<=e2.salary)
For nrd Highest salary
SELECT * FROM Emp e1 WHERE n=(SELECT count(*)FROM Emp e2 WHERE e1.salary<=e2.salary)


If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

1 comments:

jamir sande on July 14, 2012 at 8:14 AM said...

select top 1 salary from
(select top nth salary from emp order by salary desc) a order by salary


Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top