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



No comments :

Post a Comment