CodeSnip: Selecting nth Highest Value of a Column Using Oracle
Published: 04 Feb 2008
In ths code snippet, Deepankar demonstrates how to select the nth highest value of a column from any table in an Oracle database. He begins by introducing a SQL query and providing a short explanation about it. The final sections of the article examine how to perform validation in two different code samples by querying a table.
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 23668/ 49


This code snippet will allow the viewer to select the nth highest value of a column from any table in Oracle database. This article also deals with the other possible methods of selection along with the risks involved in using them.


Oracle 9i or above versions


In order to find nth maximum record of EMPLOYEE_ID column, the query in listing 1 first does a self-join on itself in the sub query.

Listing 1

select EMPLOYEE_ID from emp a where n=
(select count(*) from emp b where a.EMPLOYEE_ID <= b.EMPLOYEE_ID); 


There each EMPLOYEE_ID is compared with all the EMPLOYEE_IDs including itself to return the count =n for that EMPLOYEE_ID which is n times less than or equal to all the EMPLOYEE_IDs present including itself.

Let me explain with the following example.

Say we have a table having two fields like name and ID as follows.

        ID    N

      ---------- -

         1    D

         2    E

         3    F

         4    P

Now say we need to find the 2nd max id from the table, which is 3 in this case.

Our query compares each id with all the other ids including itself to find which id is twice less than or equal to as compared to others.

And only id 3 is twice less than or equal to as compared to all the other records (i.e. 3 <=3 and 3<=4 making the count=2).

Validation for Listing 1 through querying emp table

Listing 2

select EMPLOYEE_ID from emp a where 2=(
select count (*) from emp b where a.EMPLOYEE_ID <= b.EMPLOYEE_ID);

Figure 1

Now let us find another solution.

Listing 3



This fetches the nth highest record through a table.

The sub query does a vital role here in ranking the EMPLOYEE_ID column in descending manner.

The major functionality is achieved by rank () over () function.

It arranges the EMPLOYEE_ID column in descending order and does a ranking beginning from highest value. Such that highest EMPLOYEE_ID gets rank 1, the 2nd highest EMPLOYEE_ID gets rank 2 so on.

The following example illustrates the out come of sub query used.

Listing 4


Figure 2

Then it is the responsibility of the outer query to select the nth highest record.

Validation for Listing 3 through querying emp table

Listing 5


Figure 3


Both the methods explained above are well and good. The first one does not use any oracle function. Where it is explicitly asked not to use any functions this approach can be followed.

But the second approach does the same through RANK () OVER () function. Many other useful facts about the RANK () OVER () function can be revealed by exploring it more.

User Comments

Title: excellant   
Name: uttam
Date: 2010-01-22 8:48:00 AM
simply superb
Title: superb   
Name: Amit Verma
Date: 2009-12-14 8:19:52 AM
really good solution
Title: Nice and easy query   
Name: SB
Date: 2009-12-01 6:47:42 AM
Very much self explanatory. thanks for sharing.
Title: Another way to do it   
Name: Pat
Date: 2009-11-13 3:46:08 PM
I dunno, this SQL seems to be faster, on my database anyway:

WHERE rownum < 2;

I killed the examples after ten minutes of waiting each. My SQL ran in 2-3 minutes (depending on time of day). The table I'm running these on has over 4.5 million records.
Title: Was really helpful   
Name: Srinivas
Date: 2008-12-24 1:35:12 PM
Hi Deepankar,

I was working on oracle for the first time and ur code snippet example helped me a lot.

Title: Thanks friend   
Name: Chittaranjan
Date: 2008-04-05 6:00:00 AM
hi, deepankar. i'm really glad that u have found out this answer. it will really help others.
Title: Thanks   
Name: Aroop
Date: 2008-03-10 12:05:40 PM
Awesome Deepankar...!!
Title: Thanks Deepankar   
Name: Ajay
Date: 2008-03-10 11:58:35 AM
Hi Deepankar,

I was searching this query from last few days .
Thanks a lot for this.


Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2021  |  Page Processed at 2021-03-08 10:29:45 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search