CodeSnip: Selecting nth Highest Value of a Column Using Oracle
page 3 of 4
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 21694/ 29

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


View Entire Article

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-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 5:29:43 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search