Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17

    Unanswered: Want to get any 10 records from resultset

    Hello all,

    I want to get any 10 records from the ResultSet.

    Like first time I get 10 records next time I want to get next 10 not those which I have already get.

    For Example

    1) In First ResultSet 10 but from 0 to 10 records
    2) In Second ResultSet 10 but from 10 to 20 records
    3) In Third ResultSet 10 but from 20 to 30 records
    4) and so on upto N-1.


    What can be required sql statement?
    I had tried by using rowid but it is useless in my scenario becuase the result set is being build with combination of tables.

    Any help/tip will be highly appricated.
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Wink Use a function ...

    Hello,

    to do that use a function that returns a PL/SQL table with exact 10 row.
    I don´t know other techniques that will do that ...

    Hope that helps ?

    Regards
    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    iahmad, i understand what you said about "the result set is being build with combination of tables" but if you think about it for a minute, the result set is just a table, right?

    so, what is the primary key of this table?

    for example, if you had a query to return authors and books, where each author can have multiple books, and each book could be written by multiple authors, the result set might be

    Code:
    aid  authorname  bookISBN    booktitle
     34  Celko, Joe  1874416508  Instant SQL Programming
     34  Celko, Joe  1558605762  SQL For Smarties
     34  Celko, Joe  1558604537  SQL Puzzles and Answers
     47  Forta, Ben  078971809x  CF4 Web Application Kit
     47  Forta, Ben  0789718103  Advanced CF4 App Dev
    in this example, the result set has a primary key of aid plus bookISBN

    so when you return the first 10, save the primary key in variables, and use these variables when you query the second time

    select ... from ...
    where authorid >= variable
    and bookISBN >= variable
    order by authorid, bookISBN

    how you limit the number of records returned to 10, however, is a separate problem that depends on your database -- sql/server and access use TOP, mysql and postgresql use LIMIT, i'm not sure what oracle uses...

    rudy
    http://rudy.ca/
    Last edited by r937; 09-23-02 at 10:17.

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb Does this really work ?

    Hello,

    when I understand it correctly, he wants to use a cursor where he fetches 10 row with the first call and on second call the next 10 row and so on and so on.
    TOP and LIMIT will only limit the result set to the given value ... means if you use SELECT * FROM table LIMIT 10 ... you will only get the first 10
    records ... but you cannot fetch the next ten with any command.

    But ... if thats what you want to do "ROWNUM" is the command for ORACLE ... SELECT * FROM table WHERE ROWNUM < 11 ...

    If you need help in creating a stored function using a cursor ... just let me now and I will help you

    Hope that helps ?

    Regards
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TOP and LIMIT will only limit the result set to the given value ... means if you use SELECT * FROM table LIMIT 10 ... you will only get the first 10
    that's true, and maybe i didn't explain it well enough

    first query: select TOP 10 from table

    make a note of the pk of the 10th row returned

    second query: select TOP 10 from table where pk > saved value

    that gives you the next 10 rows, so make a note of the pk of the 10th row returned, which is the 20th overall

    and so on

    anyhow, this doesn't help our friend with oracle, does it?

    i have not tried it, but the method described in the article How to do paging with query results, i.e display first 10 row from the query and next 10 might work

Posting Permissions

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