From derby-user-return-9242-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed May 14 15:49:23 2008 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 90532 invoked from network); 14 May 2008 15:49:23 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 14 May 2008 15:49:23 -0000 Received: (qmail 15109 invoked by uid 500); 14 May 2008 15:49:23 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 15091 invoked by uid 500); 14 May 2008 15:49:22 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 15080 invoked by uid 99); 14 May 2008 15:49:22 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 14 May 2008 08:49:22 -0700 X-ASF-Spam-Status: No, hits=-0.8 required=10.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_MED,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (athena.apache.org: local policy) Received: from [207.46.51.80] (HELO outbound10-sin-R.bigfish.com) (207.46.51.80) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 14 May 2008 15:48:33 +0000 Received: from outbound10-sin.bigfish.com (localhost.localdomain [127.0.0.1]) by outbound10-sin-R.bigfish.com (Postfix) with ESMTP id B7E3B1852E65 for ; Wed, 14 May 2008 15:48:45 +0000 (UTC) Received: from mail27-sin-R.bigfish.com (unknown [10.3.40.3]) by outbound10-sin.bigfish.com (Postfix) with ESMTP id 97C4E1980054 for ; Wed, 14 May 2008 15:48:45 +0000 (UTC) Received: from mail27-sin (localhost.localdomain [127.0.0.1]) by mail27-sin-R.bigfish.com (Postfix) with ESMTP id 703D3CB019C for ; Wed, 14 May 2008 15:48:45 +0000 (UTC) X-BigFish: V X-MS-Exchange-Organization-Antispam-Report: OrigIP: 74.222.153.32;Service: EHS Received: by mail27-sin (MessageSwitch) id 1210780124388541_11538; Wed, 14 May 2008 15:48:44 +0000 (UCT) Received: from owa2.spimageworks.com (owa2.spimageworks.com [74.222.153.32]) by mail27-sin.bigfish.com (Postfix) with ESMTP id 9264717B8052 for ; Wed, 14 May 2008 15:48:43 +0000 (UTC) Received: from alpha.spimageworks.com ([172.30.104.3]) by owa2.spimageworks.com with Microsoft SMTPSVC(6.0.3790.3959); Wed, 14 May 2008 08:45:13 -0700 Message-ID: <482B0909.7010903@imageworks.com> Date: Wed, 14 May 2008 08:45:13 -0700 From: Matt Chambers Reply-To: chambers@imageworks.com Organization: Sony Pictures Imageworks User-Agent: Thunderbird 2.0.0.0 (X11/20070326) MIME-Version: 1.0 To: Derby Discussion Subject: Re: top N reporting with derby References: <482A3C51.7080202@imageworks.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------030201090101090501090708" X-OriginalArrivalTime: 14 May 2008 15:45:13.0692 (UTC) FILETIME=[7FCFE1C0:01C8B5D9] X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. --------------030201090101090501090708 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 Middle-Tier Homepage --------------030201090101090501090708 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 Middle-Tier Homepage
--------------030201090101090501090708--