Re: getting X rows at a time via JDBC (i.e. no cursors)

>
> select * from nm_user_info where user='joe' and rownum between 1 and 10;
>
> I can't do the latter two because rows that match "user='joe'" part are
> not necessarily one after the other (other users' data may be between
> joe's rows) so "between 1 and 10" wouldn't work (there may be only 4
> rows between rowid/rownum 1 and 10 for example).
>

But rownum is NOT the phzsical number of the row in the table (that would be rowid in some way).
Rownum is the number of the retrieved result-set BEFORE any ORDER BY...

So, it doesn't matter whether there exist some tupel in the table whose column user != 'joe'.....

the statement

select

    rownum, tab.*
from nm_user_info tab
where tab.user='joe';

will always return rownums starting at 1 with no gaps.

For your problem the solution will be (as I understand your problem):

select sub.*
from

    (
    select rownum num , tab.*
    from nm_user_info tab
    where tab.user = 'joe'
    ) sub
where sub.num between X and X+10;

where X is the start-rownum

But this statement can become slow if the number of tupel in the table is large....

Regards, Stephan

--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-0           | Landsberger Allee 392
fax: +49 30 549932-21          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------
       PGP-Key verfügbar       |      PGP-Key available
---------------------------------------------------------------