Menu

Monday 21 January 2013

How to find multiple employee(s) having 'n'th highest salary (when more than one employee are having same salary)


CREATE TABLE EMP_SALARY(EMP_ID INT,SALARY INT)

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

SELECT * FROM EMP_SALARY

--Output:
EMP_ID      SALARY
----------- -----------
1           10000
2           5000
3           6000
4           6000
5           5000
6           6000

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

--Result:
EMP_ID      SALARY
----------- -----------
3           6000
4           6000
6           6000

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

--Result:
EMP_ID      SALARY
----------- -----------
3           6000
4           6000
6           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
4           6000
6           6000

                        How to find employee having 'n'th highest Salary>>

No comments:

Post a Comment