Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (nike.apache.org: local policy)
X-MS-Exchange-Organization-Antispam-Report: OrigIP: 74.222.153.32;Service: EHS
Received: by mail89-va3 (MessageSwitch) id 1210727506908632_19436;
 Wed, 14 May 2008 01:11:46 +0000 (UCT)
Received: from owa2.spimageworks.com (owa2.spimageworks.com [74.222.153.32])
	by mail89-va3.bigfish.com (Postfix) with ESMTP id B042D25806E
	for <derby-user@db.apache.org>; Wed, 14 May 2008 01:11:46 +0000 (UTC)
Received: from alpha.spimageworks.com ([172.30.104.3]) by
 owa2.spimageworks.com with Microsoft SMTPSVC(6.0.3790.3959);
	 Tue, 13 May 2008 18:11:46 -0700
Message-ID: <482A3C51.7080202@imageworks.com>
Date: Tue, 13 May 2008 18:11:45 -0700
From: Matt Chambers <chambers@imageworks.com>
Reply-To: chambers@imageworks.com
Organization: Sony Pictures Imageworks
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: top N reporting with derby
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-OriginalArrivalTime: 14 May 2008 01:11:46.0144 (UTC)
 FILETIME=[7A7AAE00:01C8B55F]
X-Virus-Checked: Checked by ClamAV on apache.org

Hi guys, new to the list.   I'm a new Derby user, just grabbed the 
latest version.

I have a Tomcat application that sends some statistics to a server for 
processing.  The statistics are not mission critical or anything, they 
are just nice to have, so if the statistics server goes down, no big 
deal.  Its actually never gone down but in case it does, I would like to 
spool the updates into a Derby DB.  So I changed the code around a bit 
to always store and forward instead of sending directly to the stats 
server using Derby but I'm stuck.

I have two requirements.
1. that if the stats server is down for days that I should be able to 
process the spool in small chunks over time when it comes back up.
2. each row has to be processed in the order in which i was placed in 
the table or else the stats server could drop them anyway.

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?

Any ideas?

-Matt