db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Six Fried Rice <tech...@sixfriedrice.com>
Subject Re: top N reporting with derby
Date Wed, 14 May 2008 15:12:21 GMT
On May 13, 2008, at 6:11 PM, Matt Chambers wrote:

> Normally, I would do a SELECT * FROM spool_table ORDER BY  
> date_inserted ASC LIMIT 100 to grab the oldest entries but Derby  
> can't do limit and you can't user ORDER BY in sub selects.  So how  
> do I get my data out of the table in small chunks in the same order  
> it went in?

Matt:

You can write your query like this:

SELECT ROW_NUMBER() OVER() as rownum, spool_table.* FROM spool_table  
ORDER BY date_inserted WHERE rownum <= 100

And it should do the trick. This requires Derby 10.4.

Hope it helps :)

Geoff

Mime
View raw message