Results 1 to 7 of 7

Thread: sql query

  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: sql query

    Hi,

    How do you retrieve records from the resultset based on pages. that is if there are 2000 records in the database, so we want to print 100 records per page. how do we get this one? reply soon, here iam maintaining the pagination using servlets and jsp.

  2. #2
    Join Date
    Dec 2003
    Posts
    74
    how about

    page 1

    select
    name
    from
    customers
    where
    rownum < 101


    page 2

    select
    name
    from
    customers
    where
    rownum between 101 and 200

    etc....



    regards


    edwin

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    edwin,
    your second query (select
    name
    from
    customers
    where
    rownum between 101 and 200
    )

    will not work

    --jaggu

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Return the entire record set and apply the formatting within the JSP page.

    If you want to select each page through sql, for which you would have to supply 'a' from the jsp page then,

    Select *
    from
    (select columns, rownum as rn
    from table
    order by criteria)
    where rn between a AND b;

    I strongly recommend performing such tasks on the client / application side (option 1).
    Last edited by r123456; 12-30-03 at 13:13.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Dec 2003
    Posts
    74
    correct but this will

    ---------------------------------
    select
    name
    from
    (select rownum rowcount , ops_customers.* from ops_customers)
    where
    rowcount between 100 and 201
    -----------------------------------

  6. #6
    Join Date
    Oct 2003
    Posts
    87
    I'd leave the pagination in the code, bouncing back and forth to the DB Server with new SQL each time isn't a good idea. Just open a cursor and fetch 100 at a time.
    Oracle - DB2 - MS Access -

  7. #7
    Join Date
    Aug 2003
    Posts
    123
    This is a good Idea.

Posting Permissions

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