Thank you, Ali! I'll take a look... -----Mensagem original----- De: Suavi Ali Demir [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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 > > >
