RES: how to fetch specific number of rows?

[Paulo Alvim <al...@powerlogic.com.br>]

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 <al...@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
>
>
>

Correction: RES: how to fetch specific number of rows?

[Suavi Ali Demir <de...@yahoo.com>]

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 <al...@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 <al...@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
> >
> >
> >
> 
> 
> 
> 
>