DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Limit results from a query, and selective results.

  1. #1
    Join Date
    Mar 2002
    Posts
    1

    Unhappy

    I was reading the article here:
    http://www.inquiry.com/techtips/orac...0min1200-3.asp

    I am working from an oracle 8i database with a few thousand records in it.

    Can you help me out here? I tried the example method, and it appears to me that it is only returning sets of data
    that are below the ROWNUM I request. For example, if I use .... WHERE ROWNUM<100 ... It looks like it only gets data from the first 100 rows of the table instead of a count of the query's result row numbers.

    When I put a very loose term into the search, it should return the max, 100 lines, but only returns 87. I know one query produces 156 results, but this limit cuts off at 60.

    ???

    I really would like to be able to show 30 results per page and have users click 'next page' to view the next 30. It seems absurd to me that with Postgress, or MySQL I can do this very easily with ... LIMIT 100,30 .... but the expensive Oracle DB does not have the capability? I don't know.

    Please, if you know you can help. (PLEASE!)

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Not optimal, but should work:

    select isql.*
    from
    (select tb.*, rownum rn
    from TABLE_NAME tb
    ) isql
    where isql.rn between BEGIN_ROW and END_ROW;

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Search for WINDOW ROWNUM in this forum - lots of exampes of how to use ROWNUM for windowing properly

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

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


Click Here to Expand Forum to Full Width