db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suavi Ali Demir <dem...@yahoo.com>
Subject Correction: RES: how to fetch specific number of rows?
Date Mon, 01 Aug 2005 16:37:37 GMT
The sentence:

"According to their example, if you wanted to select
more rows, it would look something like below:"

should read:

"According to their example, if you wanted to select
more COLUMNS, it would look something like below:"

Ali


--- Paulo Alvim <alvim@powerlogic.com.br> wrote:

> Thank you, Ali! I'll take a look...
> 
> -----Mensagem original-----
> De: Suavi Ali Demir [mailto:demir4@yahoo.com]
> Enviada em: sábado, 30 de julho de 2005 21:57
> Para: Derby Discussion
> Assunto: Re: how to fetch specific number of rows?
> 
> 
> Hi Paulo,
> 
> This asp page discusses different ways of doing what
> you want:
> http://www.aspfaq.com/show.asp?id=2120
> 
> One of the easy SQL ways discussed there is:
> 
> To fetch 5 rows at a time and get the 3rd page you
> would use this sql:
> 
>     SELECT
>         a.empno
>     FROM
>         employee a
>         INNER JOIN employee b
>     ON
>         a.empno>=b.empno
>     GROUP BY
>         a.empno
>     HAVING
>         COUNT(*) BETWEEN 11 AND 15
>     ORDER BY
>         a.empno
> 
> According to their example, if you wanted to select
> more rows, it would look something like below:
> 
> 
>     SELECT
>         a.firstname, a.midinit, a.lastname
>     FROM
>         employee a
>         INNER JOIN employee b
>     ON
> 	a.firstname||a.midinit||a.lastname >=
> b.firstname||b.midinit||b.lastname
>     GROUP BY
>          a.firstname, a.midinit, a.lastname
>     HAVING
>         COUNT(*) BETWEEN 11 AND 15
>     ORDER BY
>          a.firstname, a.midinit, a.lastname
> 
> Being forced to ORDER BY all columns you select
> should
> be OK since your pages need to come in some order.
> However I have no idea how this sql would perform,
> especially for many columns. Also, while doing
> a.firstnme||a.midinit||a.lastname you may get
> unintended result but going the simple
> a.firstname>=b.firstname way does not work.
> 
> 
> I think in JSF, the way Sun's table impl implements
> this is that they get a ResultSet and loop though
> the
> rs.next() until they reach the page they want. That
> may perform better in some cases (especially in
> embedded mode Derby, since you won't send extra rows
> over the wire).
> 
> Regards,
> Ali
> 
> 
> --- Paulo Alvim <alvim@powerlogic.com.br> wrote:
> > Hi!
> >
> > I'm trying to use Derby to fetch "blocks" of X
> > records each time in that
> > kind of "web navigator" ("20 records from 100 to
> > 120"). Ex: I use "rownum"
> > in Oracle...
> >
> > How can I do it using Apache Derby? (Of course we
> > can't keep cursors opened,
> > etc.)
> >
> > Thanks in advance!
> >
> > Paulo Alvim
> > Powerlogic
> > Brazil
> >
> >
> >
> 
> 
> 
> 
> 


Mime
View raw message