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