Menu

Monday 21 January 2013

How to find employee having 'n'th highest Salary


/* I have explained a scenario where we want to know emplyee having 2nd highest salary */

CREATE TABLE EMP_SALARY(EMP_ID INT,SALARY INT)

INSERT INTO EMP_SALARY VALUES(1,10000),(2,5000),(3,6000)

SELECT * FROM EMP_SALARY

--Output:
EMP_ID      SALARY
----------- -----------
1           10000
2           5000
3           6000

--1st method using 'TOP'
SELECT TOP 1 A.*
FROM EMP_SALARY A
WHERE A.EMP_ID IN ( SELECT TOP 2 B.EMP_ID
                    FROM EMP_SALARY B
                    ORDER BY B.SALARY DESC)
ORDER BY A.SALARY

--Result:
EMP_ID      SALARY
----------- -----------
3           6000

--2nd method using 'COUNT'
SELECT A.*
FROM EMP_SALARY A
WHERE 2=(SELECT COUNT(B.SALARY)
         FROM EMP_SALARY B
         WHERE B.SALARY>=A.SALARY)

--Result:
EMP_ID      SALARY
----------- -----------
3           6000

--3rd method using 'DENSE_RANK'
SELECT A.EMP_ID,A.SALARY
FROM (SELECT EMP_ID,SALARY,
             DENSE_RANK() OVER(ORDER BY SALARY DESC) AS SALARY_ORD
      FROM EMP_SALARY)A
WHERE A.SALARY_ORD=2

--Result:
EMP_ID      SALARY
----------- -----------
3           6000


No comments:

Post a Comment