how to retrieve records from mth to nth rows in order form?

hi everybody,

i hv some doubts here....hoping that ppl here can help me ....

say i want to select records from row 1 to 5 of a table, so i wrote this sql statement:

1. select * from tablename where rownum <=5 minus select * from tablename where rownum <1;

if i want to sort it within the recordset (from row 1 to 5), i can simply issue another sql statement:

2. select * from tablename where rownum <=5 minus select * from tablename where rownum <1 order by something;

but now my problem comes in....say the table has 100 records in total, so each time i just want to select 10 records out of the 100. At first i select from row 1 to 10, then 11-20,
21-30, 31-40 ... and so on. The problems comes in bcoz of the sorting part. No.2 sql statement works fine but doen't meet my requirement, bcoz it only sorts within the recordset of row 1 to 10, but what i want is to get a record set from row 1 to 10 but the order should take all the other 90 records into account as well. In short, what i want should be like this:

i. select * from tablename order by something; ii. After obtaining this resultset, then when i issue the sql statement written above, it shall return me the recordset from row 1-20 in order.

so, my question is: how to combine the above two steps into one single sql statement? izzit possible?

Any helps would be greatly appreaciated!!

lkw.

p/s: sorry so long winded....*grin* Received on Sun Dec 19 1999 - 21:23:17 CST