Saturday, September 24, 2016

Oracle SQL - Nth highest salary


Below are the queries for finding nth highest salary of employees:

Using correlated subquery:


SELECT e1.salary,e1.*

  FROM employees e1

 WHERE (n-1) = (SELECT COUNT(DISTINCT(e2.Salary))

                FROM employees e2

               WHERE e2.Salary > e1.salary);

Note : where n is the number of the highest salaried employee

  
Using dense_rank() analytics function;   

 

SELECT *

  FROM (SELECT salary, dense_rank() OVER(ORDER BY salary DESC) AS ranking

          FROM employees) emp

 WHERE emp.ranking = n;


 Note : where n is the number of the highest salaried employee



Examples:

Below query shows the employees salaries in descending order.






Below query shows  the 2nd highest salaried employee using correlatd subquery.




Below query shows the 1st highest paid salary



Featured Post

Will the data from Oracle Database be lost after formatting the system in which it is installed?

So Someone asked me a question,  Will the data from Oracle Database be lost after formatting the system in which it is installed? Since the ...

Popular Posts