How to get number of rows {FOLLOWUP QUESTION FROM ARCHIVES }
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to get number of rows {FOLLOWUP QUESTION FROM ARCHIVES }

  1. #1
    Join Date
    Mar 2001
    Posts
    19
    Team,

    I am struggling to get the data from the database page by page. I went thro the archives of this forum and i found this query to be very useful and similar to my requirement.

    It works like charm for the first page ( i.e. 0-10) but for the next page it is not working as i expected.

    There are 11 rows in the table. When i do a query for 0-10 it is working perfectly like the one below.

    Here is the query
    --------------------
    select messageID, parentID, title, author, updatedTime
    from
    (select messageID, parentID, title, author, updatedTime
    from
    (select messageID, parentID, title, author, updatedTime
    from ent_bulletin_messages
    where sectionID=10
    order by messageID, parentID)
    where rownum < =11)
    where rownum > 0

    BUT WHEN I CHANGE THE PARAMETERS TO
    rownum <=20
    where rownum > 10

    IT SAYS NO ROWS SELECTED.

    I DONT KNOW HOW TO MAKE THAT HAPPEN.

    Can anyone please please help me out.

    Thanks a million
    Seenu




  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Your statement is not quite correct.

    Search for 'Window ROWNUM' on this forum and follow all the threads on this topic.

    Here is one of them:
    http://www.dbasupport.com/forums/sho...?threadid=5480

    Here is the example from the threads:

    SELECT
    ---OUTER.C1
    FROM
    ---(
    ------SELECT
    ---------INNER.C1,
    ---------ROWNUM
    ------------AS QUERY_ROWNUM
    ------FROM
    ---------(
    ---------SELECT
    ------------C1
    ---------FROM
    ------------TABLE1
    ---------ORDER BY
    ------------C1
    ---------) INNER
    ------WHERE
    ---------ROWNUM < 8
    ---) OUTER
    WHERE
    ---OUTER.QUERY_ROWNUM >= 4

    Notice how the rownum is aliased between the second and third layer.

    HTH,

    - Chris

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