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.

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

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

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/

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

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