db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Chambers <chamb...@imageworks.com>
Subject Re: top N reporting with derby
Date Wed, 14 May 2008 15:45:13 GMT
Six Fried Rice wrote:
> 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
>
Thanks for response.  I tried that one, was a no go.

-bash-3.1$ ./ij
ij version 10.4

That query gives me:
ERROR 42X01: Syntax error: Encountered "WHERE" at line 1, column 75.


-- 
-Matt

Cue3 Development Progress 
<http://twiki.spimageworks.com/twiki/bin/view/Software/CueDevelopment> 
Middle-Tier Homepage 
<http://twiki.spimageworks.com/twiki/bin/view/Software/MiddleTier>

Mime
View raw message