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
No comments:
Post a Comment