Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Limiting rows returned

    Hi all,

    I need to return a subset of rows from a table (rows 10 - 20 for example) but can't find a way to do this.

    Using MySQL I'd use Limit but this is not available. Any ideas? I'm sure its just me being an Oracle novice. Any and all help would be appreciated.

    Cheers

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You can do the following

    select *
    from
    (
    select rownum rnum, n.*
    from
    (
    select * from table_x order by y
    ) n
    ) where rnum between 10 and 20

    You can also use the row_number() analytic function.

    Alan

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    Thank you very much. That works perfectly.

    Cheers

  4. #4
    Join Date
    Oct 2003
    Posts
    2

    delete and limiting rows

    How can I delete the limited rows?
    Thanks.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Is this what you are after?

    Alan

    delete from table_x
    where (<list of primary key columns>) in
    (
    select <list of primary key columns>
    from
    (
    select rownum rnum, n.*
    from
    (
    select * from table_x order by y
    ) n
    ) where rnum between 10 and 20
    )

  6. #6
    Join Date
    Oct 2003
    Posts
    2

    Smile

    Thank you so much.
    ~aTan
    Originally posted by AlanP
    Is this what you are after?

    Alan

    delete from table_x
    where (<list of primary key columns>) in
    (
    select <list of primary key columns>
    from
    (
    select rownum rnum, n.*
    from
    (
    select * from table_x order by y
    ) n
    ) where rnum between 10 and 20
    )

Posting Permissions

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