March 13, 2016

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

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) UNION SELECT MAX(SALARY) FROM EMPLOYEE);
 

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


Also Read : Oracle and MySql : Find Top 3rd highest salary row from employee table

0 comments:

Disclaimer

We shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its response or damage to your system. We do not guarantee that the integrity or security of this communication has been maintained or that this communication is free of viruses, interceptions or interferences. Anyone communicating with us by email accepts the risks involved and their consequences. We accept no liability for any damage caused by any virus transmitted by this site.