Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    8

    Question Unanswered: limiting the number of records returned

    hi all,

    i've seen this question posed in various places and seem to be missing something simple. i'm using oracle's sql plus.

    i want to run a select statement that will only return the first 10 records. i need to specify both criteria for which records to return and order them by one field descending. from these results i want to only see the first 10 records.

    i've seen mention of the rownum to limit returns, for example....

    select * from table where field = value and rownum < value.

    my problem is that i also need to ORDER BY, and the rownum is getting processed before the ORDER BY, so it doesn't return what i need.

    for example, i can get the following statement to execute....

    select * from table where field = value and rownum < value order by field desc.

    this statement returns the specified number of rows and then orders them. i need the opposite. i need the statement to order the rows and then return the specified number of records.

    i've also seen

    select field from (select * from table where field = value order by field) where rownum < 11

    but i get an error with this about the right parenthasis missing. this error does not occur if i leave out the ORDER BY part in the sub query.

    how do i get what i need?

    thanks,

    glenn

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Glen,

    your planned solution should be fine, I've just run this query here on an 8.1.7 database - it's fine....

    select * from (select * from r_acct order by id) where rownum < 10

    Maybe your db version, compatible parameter etc have an effect? If so there are other chaps here with more experience of the different versions who might be able to help. Posting your relevant db version info might shed some light on it.

    Cheers
    Bill

  3. #3
    Join Date
    Sep 2002
    Posts
    8
    thanks for the reply. i'm not sure about the actual database, but the oracle client i'm using to run my queries is sql plus 8.0. does this explain why the mentioned solution is not working for me? any other suggestions?

  4. #4
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107
    Originally posted by gacaccia
    thanks for the reply. i'm not sure about the actual database, but the oracle client i'm using to run my queries is sql plus 8.0. does this explain why the mentioned solution is not working for me? any other suggestions?
    Hi,
    The method Bill suggested is the best and the easiest method.
    The other work around for the same would be
    (Done on Emp Table)

    SELECT ROWNUM, a.*
    FROM emp a ,
    dual b
    WHERE a.ename = b.DUMMY(+)
    AND ROWNUM < 6
    ORDER BY ename

    Hope it help.
    nn

Posting Permissions

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