Thursday 22 June 2017

SQL : Find 2nd or 3rd highest salary from employee table without ROWNUM or TOP ~ GNIITHELP

Employee Table
SELECT SALARY FROM EMPLOYEE ORDER BY SALARY DESC;

OUTPUT :  
SALARY
----------
     77777
     60000
     34000
     23232


Example to find the 2rd highest salary 

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE);
 

OUTPUT :
MAX(SALARY)
-----------
      60000


Example to find the 3rd highest salary
After union query is omitting first highest salary. 
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE) UNIONSELECT MAX(SALARY) FROM EMPLOYEE);
 

OUTPUT :
MAX(SALARY)
-----------
      34000

No comments:

Post a Comment