db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paulo Alvim" <al...@powerlogic.com.br>
Subject RES: how to fetch specific number of rows?
Date Mon, 01 Aug 2005 15:39:44 GMT
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