Nth Highest Salary in Oracle

May 17, 2010

in Analytics, FAQ, SQL

Nth Highest Salary

“How can I select the Nth highest salary of the EMP table?”

This is a question that every Oracle newbie stumbles over. Ask it on a forum and you’re pointed to the archives. That gets you nowhere as when you search the archives, all you find is a host of other messages also asking you to search the archives.

Here comes the answer to the problem of finding the Nth highest salary. You can extend this logic to find the Nth highest row of any table.

First things first: The question is ambiguous!

Let’s say this is your data:

Name Salary
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2850
ALLEN 1600

Who is second – FORD or SCOTT or both?

What will you say about JONES’s salary – is it the 3rd highest salary, or the 4th highest?

If you are looking for the set of people earning the Nth highest salary, with no gaps in case of ties, then JONES should be ranked 3rd, after KING [5000, 1st], followed by FORD and SCOTT [both 3000, 2nd].

If you are looking for exact ranks with gaps if there are ties, then JONES is the 4th highest paid employee, as there are 3 people earning more than him – KING, FORD and SCOTT. In this system of ranking, FORD and SCOTT are 2nd jointly and no employee is 3rd.

This is how your ranks will look, in the 2 cases:

Name Salary Rank
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 3
BLAKE 2850 4
CLARK 2850 4
ALLEN 1600 5
Name Salary Rank
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 4
BLAKE 2850 5
CLARK 2850 5
ALLEN 1600 7

Scenario 1: No gaps in case of ties                                 Scenario 2: Gaps in case of ties

Once you have your question sorted out –

(a) Set of people earning the Nth highest salary, with continuous ranks if there are ties, OR

(b) Set of people earning the Nth highest salary, with skipped rank numbers if there are ties

Then you can proceed to writing the queries.

Scenario 1: DENSE_RANK () for Nth highest row, no gaps in case of ties

The analytic function dense_rank() will rank the rows with no gaps in ranking sequence if there are ties.

The ranks are calculated as:

SQL> select ename
2   ,sal
3   ,dense_rank() over (order by sal desc) ranking
4   from     emp;

ENAME       SAL       RANKING
---------- ---------- ----------
KING        5000      1
FORD        3000      2
SCOTT       3000      2
JONES       2975      3
CLARK       2850      4
BLAKE       2850      4
ALLEN       1600      5

Wrap a filter around and pick out the Nth highest salary, say the 4th highest salary.

SQL> select *
2   from
3   (
4       select ename
5       ,sal
6       ,dense_rank() over (order by sal desc) ranking
7       from     emp
8   )
9   where ranking = 4 -- Replace 4 with any value of N
10   /

ENAME                         SAL       RANKING
---------- ---------- ----------
BLAKE                       2850                   4
CLARK                       2850                   4

The 4th position has a tie between BLAKE and CLARK.

Scenario 2: RANK () for Nth highest row, gaps in case of ties

The analytic function rank() will rank the rows with gaps in ranking sequence if there are ties.

The ranks are calculated as:

SQL> select ename
2               ,sal
3               ,rank() over (order by sal desc) ranking
4   from     emp;

ENAME      SAL        RANKING
---------- ---------- ----------
KING       5000                   1
FORD       3000                   2
SCOTT      3000                   2
JONES      2975                   4
CLARK      2850                   5
BLAKE      2850                   5
ALLEN      1600                   7
TURNER     1500                   8

Wrap a filter around and pick out the Nth highest salary, say the 4th highest salary.

SQL> select *
2   from
3   (
4       select ename
5             ,sal
6             ,rank() over (order by sal desc) ranking
7       from  emp
8   )
9   where ranking = 4 -- Replace 4 with any value of N
10   /

ENAME                         SAL       RANKING
---------- ---------- ----------
JONES                       2975                   4

A different answer from the previous query, as there is no rank 3 because of the tied 2nd place.

Closing Notes

The requirement to “find Nth highest row” is incomplete, until the following questions are also answered:

  1. Can the result match more than one value? If not, on what basis should the one record be chosen if there is a tie?
  2. How should the subsequent records be ranked in case of ties – contiguously or with gaps?

Depending on the answer for (2), DENSE_RANK (for contiguous) or RANK (for gaps) can be used. Depending on the answer for (1), extra filter criteria can be applied to the SQL.

There are other approaches for calculating the Nth highest row, too. The next is a non-analytic approach, which works the same way as the RANK query (gaps for ties).

SQL> select ename
2   , sal
3   from emp a
4   where 3 = ( select count(*) -- Replace 3 with any value of (N - 1)
5               from emp b
6               where b.sal > a.sal)
7   /

ENAME      SAL
---------- ----------
JONES      2975

However, tests have shown the analytics approach to be more efficient than the non-analytics one for Nth highest or Top-N type of queries.

{ 44 comments… read them below or add one }

1 bhargav June 11, 2011 at 11:18 am

well…thanks for helping me

2 vijay November 20, 2011 at 12:40 am

very good answer

3 KARTHIK November 24, 2011 at 10:31 pm

nice xplanation,,,,,very simple

4 Ajith February 1, 2012 at 10:44 am

The Explanations are incredible..Keep up the good work.Thanks a ton

5 sarla March 5, 2012 at 3:33 pm

Nice explanation….

6 Milind March 6, 2012 at 9:26 am

This explanation is very easy to understand…thanx

7 sarath March 8, 2012 at 6:12 pm

cool examples good work

8 Anupama April 19, 2012 at 6:23 pm

Very useful answer, I was searching for easier and understandable explaination. It works in Oracle.
Thank you very much!!!!

9 pushpjeet cholkar May 13, 2012 at 1:06 am

Thanks Dear,
Now i got the right answer after years.Thank you very much God Bless You.

10 munin June 1, 2012 at 12:38 pm

Thanx a lot…….

11 Nagarajan August 16, 2012 at 11:44 pm

CLEAR !! and Very useful.

12 Baishali Nath September 9, 2012 at 4:42 pm

Very Helpful… Thank you

13 shri October 5, 2012 at 8:18 pm

Very good answers……

14 Hanumant October 19, 2012 at 5:28 pm

thanks..nice

15 sudha October 24, 2012 at 10:06 pm

Can anyone explain the execution process of this query.. i m unable to understand this query execution please any one help me..
select ename, sal from emp a
where 3 = ( select count(*)from emp b where b.sal > a.sal)

16 pravallika October 25, 2012 at 6:13 pm

thanks..nice example

17 HemaShekar January 23, 2013 at 12:08 pm

Terrific explanation. Thanks.

18 R.Kiran February 11, 2013 at 4:56 pm

Thanks a lot…

19 Kothai March 23, 2013 at 11:28 pm

Awesome!

20 Kiran April 30, 2013 at 6:50 pm

Thanks For the Info, This is awesome

21 Jitendra May 19, 2013 at 1:29 am

I have a doubt, if we use single select sql as :
select ename ,sal ,rank() over (order by sal desc) ranking from emp where ranking = 4
this should be better, or is there any issue with this query

22 varun May 25, 2013 at 1:51 pm

hello jitendrea

this query will not run its will show u a warning message about ranking is invalid identifire
so you have to use this inside the braces

23 Manjul Pant June 28, 2013 at 9:48 am

Thanks Alot…..

24 Pooja chopda September 26, 2013 at 2:37 pm

thanks and It is very help full.

25 Sushma October 11, 2013 at 6:13 pm

Wonderful answer. Thanks a lot

26 Sushma October 11, 2013 at 6:18 pm

I had another doubt, both the results using DENSE_RANK() and RANK() are very different.
My question is which was the right result?

27 ankush October 18, 2013 at 9:07 am

awsum..that was clear explaination. .

28 Meda Vinod Kumar January 17, 2014 at 11:59 am

thanks bro/madam,
it is very useful to me.

29 Dolly Singh February 4, 2014 at 8:23 pm

Thanku so much……..the way of demonstrating tha concept was so good and clear…….It helped me a lot……Thanks a lot again 🙂

30 Rishitha May 30, 2014 at 8:27 am

Hi Thank you so much!!

31 Anusuya June 25, 2014 at 12:32 pm

can anyone explain that how to write a oracle query to display the employee earning the highest salary ?

32 alpana August 2, 2014 at 4:16 am

Hi,
Thanks! Very simple 🙂

33 Tushar patel April 16, 2015 at 1:36 am

Hi,
To find the nth highest salary where clause is used and with where clause the the term ranking is used which is alias. But in oracle database alias can not be used with where clause…then how it is correct ans…it will give error…ORA:00904: invalid identifier..

SQL> select *
2 from
3 (
4 select ename
5 ,sal
6 ,rank() over (order by sal desc) ranking
7 from emp
8 )
9 where ranking = 4 — Replace 4 with any value of N
10 /

34 oratabler April 16, 2015 at 8:35 am

@Tushar Patel: Did you run the SQL to verify that claim?

The alias ‘ranking’ has been used in an inline view, not in the main SQL’s WHERE clause.

35 jayanthi August 29, 2015 at 7:22 am

thank you… after a long time i got the nice simple answer

36 Rajani kanta October 13, 2015 at 1:32 am

excellent explanation

37 dinesh January 29, 2016 at 6:24 pm

Thanks

38 devender May 5, 2016 at 10:09 am

thanks you for sharing this. This is one of best question for interview point of view.

39 ramkishan November 6, 2016 at 9:51 pm

thanks…. Very good explanation

40 sugandha November 7, 2016 at 5:16 pm

SELECT *
FROM emp Emp1
WHERE (3) = (
SELECT COUNT(DISTINCT(Emp2.sal ))
FROM emp Emp2
WHERE Emp2.sal > Emp1.sal )

–the above query will work same as dense_rank()

41 Nilesh June 19, 2017 at 7:25 pm

After that you got answer but ty there is one more droback ….

42 Papa Rao October 16, 2017 at 12:11 pm

good examples

43 Mohammad March 27, 2018 at 6:19 am

Thanks..Good Explanation..

44 krishnarjun July 26, 2018 at 9:21 am

Some issue with this query not show result for all values asked. say 2nd highest —
select ename
, sal
from emp a
where 3 = ( select count(*) — Replace 3 with any value of (N – 1)
from emp b
where b.sal > a.sal)

Leave a Comment

Previous post:

Next post: