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

Thread: ROWNUM

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    I would like to know, whether I can query a table based on the rownum.
    for example, If I want the records from 5000 to 10000 in the table : Can I use
    select * from table_name
    where rownum between 5000 and 10000;

    Badrinath

  2. #2
    Join Date
    Feb 2001
    Posts
    184
    No ...
    Rownum Changes with the Query.
    Valid Commands for Rownum
    are

    Rownum = 1 (Equal Sign only with 1)
    Rownum <= Always fine
    Nothing else is supported... You won't get any thing.

    Thanks

  3. #3
    Join Date
    Feb 2001
    Posts
    83
    You can use ROWNUM to limit the number of rows returned by a query, as in this example:

    SELECT * FROM emp WHERE ROWNUM < 10;


    Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

    SELECT * FROM emp
    WHERE ROWNUM > 1;


    The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

    with regards
    Prasanna S

  4. #4
    Join Date
    Oct 2000
    Posts
    123
    You can think things in defferent way:

    First get a result set by querying rownum < 10000,
    then you can get the result set you want by issuing rownum > 5000 based on the first result set which you already done.

    Take care

  5. #5
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    the following is what I cut down from the same forum long time ago, thanks to the contributor.

    To have records from 10 to 20
    do
    select * from emp where rownum < 21
    minus
    select * from emp where rownum < 11;

    Cheers.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    This would work as well

    select * from
    (select empno, ename, mgr, rownum as number_row from emp where rownum < 21)
    where number_row > 11;




  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    ... and pando's is much more efficient.

    Here is a thread on the topic:

    [url]http://www.dbasupport.com/forums/showthread.php?threadid=5480[/url]

    - Chris

  8. #8
    Join Date
    Jul 2000
    Location
    india
    Posts
    213
    hi

    yeah use the inline view like the one used by pando definately it will work
    thanks
    pras

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