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

In article <38F431E8.159A0F07_at_beusen.de>,   stephan.born_at_beusen.de wrote:
> >
> > 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...

You are right. Unfortunately, it really is BEFORE any ORDER BY, which means I have to have separate blocks of code for 'ASC' and 'DESC' :(

Thanks,

Otis

> 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
> ---------------------------------------------------------------
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 12 2000 - 00:00:00 CDT