Results 1 to 5 of 5

Thread: Top 100 rows

  1. #1
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28

    Top 100 rows

    In SQL Server I had a command, "TOP" with which I could get a number of lines from a table: "SELECT TOP 100 from TABLE1"
    Is there an equivalent in Oracle?
    Thank you!

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    you can stop the record set return at any number of rows with the following

    where rownum <= 100

    This will give you 100 rows of output in no paticular order.

  3. #3
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28
    Worked perfectly. Thanks!

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,431
    However, the rows returned will be RANDOM. If you want a sorted list of the top 100 then

    select col1
    from
    (select col1
    from mytable
    order by col1)
    where rownum <= 100;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Right, if you simply specify --

    SELECT col1 FROM my_table WHERE rownum < 100;

    what you will get are the first 100 records stored in the table, in no particular order. I've had developers try and "work around" it and do something like --

    SELECT col1 FROM my_table WHERE rownum < 100 ORDER BY col1;

    which looks like it will work, but doesn't because the rownum being a part of the WHERE clause gets evaluated before the ORDER BY clause.

    Hence, the solution offered by beilstwh is the best solution... btw, Oracle calls that a "Top-N Query".
    JoeB
    save disk space, use smaller fonts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •