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

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.


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 4:37:30 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search