How can I get certain records from a table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How can I get certain records from a table

  1. #1
    Join Date
    Nov 2000
    Posts
    198

    How can I get certain records from a table

    how can I write a query to get records from n to m.
    example

    SELECT column_a
    FROM table_name
    WHERE rownum BETWEEN 10 and 20;
    order by column_a

    I am trying to get records between 10 and 20 only.

    but the ROWNUM DOESN'T WORK IN THIS CASE.

    hope this makes it more clear.


    Thanks
    Last edited by ocpdude; 12-03-2004 at 11:16 AM.

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Code:
    select * from table_name
    where field_name between 10 and 20;

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Originally posted by waitecj
    Code:
    select * from table_name
    where field_name between 10 and 20;
    Or are you asking for?

    Code:
    SELECT * 
      FROM table_name
     WHERE rownum BETWEEN 10 and 20;
    this space intentionally left blank

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Vague answer given to a vague question

  5. #5
    Join Date
    Nov 2000
    Posts
    198
    SELECT *
    FROM table_name
    WHERE rownum BETWEEN 10 and 20;

    but the rownum doesn't work

  6. #6
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    What do you see when you run this query then?

  7. #7
    Join Date
    Nov 2000
    Posts
    198
    1 select agreement_id from
    2 br_agreement
    3 where rownum between 1 and 5
    4* order by agreement_id
    SQL> /

    AGREEMENT_ID
    ---------------
    RW97979748
    0012471TIH
    030710305N
    030720304N
    06-06-03

    SQL> ed
    Wrote file afiedt.buf

    1 select agreement_id from
    2 br_agreement
    3 where rownum between 10 and 20
    4* order by agreement_id
    SQL> /

    no rows selected


    as you can see from the above query anything greater than 1 returns
    nothing.

    Thanks,

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: How can I get certain records from a table

    Originally posted by ocpdude
    WHERE rownum BETWEEN 10 and 20
    Will NEVER return any thing.

    You need something like:
    Code:
    select * from
    (select a.*, rownum rnum
     from   a
     where  rownum =< 20)
    where rnum >= 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.
    Last edited by DaPi; 12-03-2004 at 11:38 AM.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Come to think of it, if you want meaningful results you'd better order by something, otherwise the result is effectively random:
    Code:
    select * from
    (select a.*, rownum rnum
     from   
     (select * from my_table
      order by something) a
     where  rownum =< 20)
    where rnum >= 10

  10. #10
    Join Date
    Nov 2000
    Posts
    198
    Thanks

    I tried your first query with the ORDER BY and it worked fine.

    select COL_1 from
    (select a.COL_1, rownum rnum
    from TABLE a
    where rownum <= 20
    OREDER BY COL_1)
    where rnum >= 10

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