CodeSnip: Selecting nth Highest Value of a Column Using Oracle
 
Published: 04 Feb 2008
Abstract
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
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 25117/ 64

Introduction

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.

Requirements

Oracle 9i or above versions

Working

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); 

Explanation

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

Select EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID ,RANK() OVER 
(ORDER BY EMPLOYEE_ID DESC) DEPTRANK FROM emp) WHERE DEPTRANK=n;

Explanation

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

SELECT EMPLOYEE_ID, RANK () OVER (ORDER BY EMPLOYEE_ID DESC) DEPTRANK FROM emp;

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

SELECT EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID, RANK () OVER 
(ORDER BY EMPLOYEE_ID DESC) DEPTRANK FROM emp ) WHERE DEPTRANK=2;

Figure 3

Conclusion

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

SELECT EMPLOYEE_ID
FROM (SELECT EMPLOYEE_ID FROM emp ORDER BY EMPLOYEE_ID desc)
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
Comment:
Hi Deepankar,

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

Thanks
Title: Thanks friend   
Name: Chittaranjan
Date: 2008-04-05 6:00:00 AM
Comment:
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
Comment:
Awesome Deepankar...!!
Title: Thanks Deepankar   
Name: Ajay
Date: 2008-03-10 11:58:35 AM
Comment:
Hi Deepankar,

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

Regards,
Aj






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


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-12-11 7:38:49 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search