Categories
Contact Doug!
Learn About Doug!
View Doug Boude's online resume
updated 11/18/2009

View Doug Boude's profile on LinkedIn
Link to me!

Follow Doug Boude on Twitter
Follow me!

Be Doug's friend on Facebook
Befriend me!
(I promise not to follow you home)
OO Lexicon
Chat with Doug!
Recent Entries
You may also be interested in...
Web Hosting

<< September, 2014 >>
SMTWTFS
123456
78910111213
14151617181920
21222324252627
282930
Search Blog

Recent Comments
Re: My Top 20 Life Lessons for Boys and Young Men (by jeffrey scott berry at 9/16 2:42 PM)
Re: Equivalent of SQL "TOP X" in Oracle (by Mark Foster at 7/07 4:04 PM)
Re: SQL Forward Engineering with Visio 2003 Professional (by Thomas at 6/26 4:41 AM)
Re: One Shot Query to Recalculate Orderby Field - MySQL (by gary at 6/17 6:46 PM)
Re: DON'T GET SICK IN ARKANSAS! (by r. wood at 5/25 12:00 AM)
Re: SQL Forward Engineering with Visio 2003 Professional (by Andrew at 4/30 6:14 AM)
Re: Basic Ajax Select List Filter in PHP (by good at 2/04 5:26 AM)
Re: Family Law: The Weapon of Choice for Woman Scorned (by swalker at 2/03 2:15 AM)
Re: Approaches to Building Strings: The Imploding Array (by bantal silikon at 2/01 9:44 PM)
Re: Disappearing IE Popup Window During Save/Open Dialog (by AddisonDean at 1/15 9:59 AM)
Re: My Top 20 Life Lessons for Boys and Young Men (by Alex at 1/13 8:45 PM)
Re: Array Loop Modifications in CFSCRIPT (by Alex at 11/25 11:18 AM)
Re: Array Loop Modifications in CFSCRIPT (by Abram at 11/14 11:32 PM)
Re: Recursive Functions in ColdFusion (by Dwayne at 10/25 3:47 PM)
Re: Porting Coldfusion Code to Mura (by dh at 10/16 10:14 AM)
Re: Viewing Option Text (in IE7) that's Wider than the Select List (by Devil May Cry at 9/26 1:38 AM)
Re: Why I Hate ORMs (a solicited rant) (by guideX at 9/12 11:38 PM)
Re: Recursive Functions in ColdFusion (by KP at 8/08 7:13 PM)
Re: American Airlines, YOU SUCK! (by Alison at 7/23 4:48 PM)
Re: SQL Forward Engineering with Visio 2003 Professional (by Harshad at 7/11 9:17 AM)
Archives
Photo Albums
Funnies (5)
Family (3)
RSS

Powered by
BlogCFM v1.11

24 June 2006
Equivalent of SQL "TOP X" in Oracle
A friend asked me a good question, so thought I'd share it and the answer for whomever it may help in the future.

Q:  What is the equivalent of the SQL statement "SELECT TOP 10 * FROM myTable" in Oracle?

A:  SELECT * FROM myTable WHERE ROWNUM < 11



Posted by dougboude at 2:06 AM | PRINT THIS POST! |Link | 4 comments
Subscription Options

You are not logged in, so your subscription status for this entry is unknown. You can login or register here.

Re: Equivalent of SQL "TOP X" in Oracle
thank you
Posted by Azzedo on July 1, 2009 at 4:39 PM

Re: Equivalent of SQL "TOP X" in Oracle
Hi mates,

Well, Firstly I have to thank you for you tip, it's pretty useful when we facing such situation.

Just to complete this article, the TOP function from SQL processes first the order by clause and after the TOP function... on oracle databases we have to do a "workaround" to get the expected result as shown below:

SELECT
BYMD
FROM
(SELECT BYMD FROM BRZ_AC_TMP_INFO ORDER BY BYMD DESC)
WHERE ROWNUM < 4

it's going to process the order by clause followed by the WHERE clause.

I know it's not even close the best solution but can save a good time.

Regards

Paulo Miguel Almeida
Twitter: @paulomigueljava
Facebook: http://www.facebook.com/paulo.miguel.almeida
Posted by Paulo Miguel Almeida on July 13, 2011 at 1:35 PM

Re: Equivalent of SQL "TOP X" in Oracle
thank you very much it worked for me.
Posted by Ashenafi Desalegn on December 6, 2012 at 5:29 AM

Re: Equivalent of SQL "TOP X" in Oracle
Interesting. I have a table in Oracle 11g that has 1,475,832 rows. I setup my query to WHERE ROWNUM > 1400000. I get no results. Shouldn't there be 75,832 rows? Thanks
Posted by Mark Foster on July 7, 2014 at 4:04 PM

Name:   Required
Email:   Required your email address will not be publicly displayed.

Want to receive notifications when new comments are added? Login/Register for an account.

Time to take the Turing Test!!!

Ten plus Fourteen equals
Type in the answer to the question you see above:

Your comment:

Sorry, no HTML allowed!