Different Ways To Find Nth Highest Salary In Oracle

To find nth highest salary in Oracle is a question basically every Oracle developer might have encountered directly or indirectly in their career. You may get numerous answers in different kind of forums. Let us try to consolidate all answers at one place. It will be easy for you to just look into a single place in future instead of rummaging through several forums and archives.

Let us say your EMP table looks like as below

Here are scripts to create EMP and DEPT table

emp table in oracle

According to our table, the highest salary earner is King, the president, second highest earners are Scott and Ford while the third highest salary earner is Jones and so on.

Different ways to find nth highest salary in Oracle

Find Nth Highest Salary Using DENSE_RANK() Function

Below is the query you can execute to find nth highest salary using dense_rank() function

Syntax

SELECT *
FROM
(
SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) nth_highest_sal
FROM emp
)
WHERE nth_highest_sal = &n;

Here you can replace n with 1, 2 as first highest, second highest salary respectively.

Example

SELECT *
FROM
(
SELECT   ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) nth_highest_sal
FROM emp
)
WHERE nth_highest_sal = 2;

Output

ENAME SAL NTH_HIGHEST_SAL
SCOTT 3000 2
FORD 3000 2

Find Nth Highest Salary Using RANK() Function

Using Rank function you can find nth highest salary as below

Syntax

SELECT e3.empno empno, e3.ename name, e3.sal salary
  FROM (
SELECT e1.sal, RANK() OVER (ORDER BY e1.sal DESC) RANK
  FROM (SELECT DISTINCT e2.sal FROM emp e2) e1 ) empx, emp e3
 WHERE RANK = &n
   AND e3.sal = empx.sal;

Here you can replace n with 1, 2 as first highest, second highest salary respectively.

Example

SELECT e3.empno empno, e3.ename name, e3.sal salary
  FROM (
SELECT e1.sal, RANK() OVER (ORDER BY e1.sal DESC) RANK
  FROM (SELECT DISTINCT e2.sal FROM emp e2) e1 ) empx, emp e3
 WHERE RANK = 2
   AND e3.sal = empx.sal;

Output

EMPNO NAME SALARY
7788 SCOTT 3000
7902 FORD 3000

Find Nth Highest Salary Using Correlated Subquery or COUNT function

This is another way to find out nth highest salary using correlated sub-query or COUNT function. Maybe this is the simplest way of all.

Syntax

SELECT empno, ename, sal
  FROM emp e1
 WHERE &n = (SELECT COUNT(DISTINCT(sal)) FROM emp e2 WHERE e2.sal >= e1.sal);

Replace n with value 1,2 to get first highest and second highest salary respectively.

Example

SELECT empno, ename, sal
  FROM emp e1
 WHERE 2 = (SELECT COUNT(DISTINCT(sal)) FROM emp e2 WHERE e2.sal >= e1.sal);

Output

EMPNO ENAME SAL
7788 SCOTT 3000
7902 FORD 300

Find Nth Highest Salary Using ROW_NUMBER() Function

Here is how you can find out nth salary using ROW_NUMBER() function

Syntax

SELECT empno as emp_number, ename as name, salary
  FROM (
SELECT empno, ename, sal as salary, ROW_NUMBER() OVER(ORDER BY Sal DESC) as nth_highest_salary
  FROM emp)
WHERE nth_highest_salary = &n;

Here you can replace n with 1, 2 as first highest, second highest salary respectively.

Example

SELECT empno as emp_number, ename as name, salary
  FROM (
SELECT empno, ename, sal as salary, ROW_NUMBER() OVER(ORDER BY Sal DESC) as nth_highest_salary
  FROM emp )
WHERE nth_highest_salary = 2;

Output

EMP_NUMBER NAME SALARY
7788 SCOTT 3000

Drawback: The only drawback of this query is employee FORD with same salary will be regarded as 3rd highest rather than 2nd highest salary as the ranking is given by row number instead of salary as in DENSE_RANK function.

Find Nth Highest Salary using LEVEL and CONNECT BY PRIOR

Here is how we can find nth highest salary using Level and Connect by Prior

Syntax

SELECT LEVEL, MAX(sal) salary
  FROM emp
 WHERE LEVEL = &n
CONNECT BY PRIOR sal > sal
GROUP BY LEVEL;

Here you can replace n with 1, 2 as first highest, second highest salary respectively.

Example

SELECT LEVEL, MAX(sal) salary
  FROM emp
 WHERE LEVEL = 2
CONNECT BY PRIOR sal > sal
GROUP BY LEVEL;

Output

LEVEL SALARY
2 3000

Drawback: The drawback in this query is that you cannot find which employee had second highest salary.

Hope you like the article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

 

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.