SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    London
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Oracle - how to limit results?

    I need to query a huge table with over 130,000 entries, but at the moment I'm developing locally so I want to take a sample of say 2000..

    How can I limit my oracle query?

    It's as simple as this at the moment:

    select *

    from xx_PEOPLE

    ORDER BY ein

    Edit:

    I'll be using Excel IQY files to grab the output from the live version, then import it to my local access db.


    Appreciate your help!

    The Leopard Man.

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,


    try this one:

    Code:
    SELECT * FROM (SELECT * FROM xx_PEOPLE ORDER BY ein) WHERE rownum <= 2000;
    As rownum is generated when the data is fetched, i.e. before the result set is ordered, you have to use an ordering sub-select.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    London
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brilliant, thanks.

    Is there anyway to specify results, say, between 500 and 1000?



    The Leopard Man

    Quote Originally Posted by kleineme
    Hi,


    try this one:

    Code:
    SELECT * FROM (SELECT * FROM xx_PEOPLE ORDER BY ein) WHERE rownum <= 2000;
    As rownum is generated when the data is fetched, i.e. before the result set is ordered, you have to use an ordering sub-select.

  4. #4
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    that would be something like this:

    Code:
    SELECT * FROM (SELECT ROWNUM as counter, p.* FROM xx_PEOPLE p ORDER BY p.ein) WHERE counter BETWEEN 500 AND 1000;
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


Bookmarks

Posting Permissions

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