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: pass (nike.apache.org: local policy)
Message-Id: <20695A9A-F870-4AF1-8AE0-1CBA5E1F779E@sixfriedrice.com>
From: Six Fried Rice <technet@sixfriedrice.com>
To: "Derby Discussion" <derby-user@db.apache.org>
In-Reply-To: <482C5E99.5010702@imageworks.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v919.2)
Subject: Re: top N reporting with derby
Date: Thu, 15 May 2008 09:17:36 -0700
References: <482A3C51.7080202@imageworks.com>
 <F9ADBE78-B787-475E-AE3B-436E0AC8DB85@sixfriedrice.com>
 <200805151309.19607.daniel@nuix.com>
 <E8451EDC-DCF9-4771-87FD-694ADC48CC19@sixfriedrice.com>
 <482C5E99.5010702@imageworks.com>

On May 15, 2008, at 9:02 AM, Matt Chambers wrote:

> Just googling around I would say that the LIMIT/OFFSET feature has  
> to be the #1 requested Derby feature of all time.

Paging is such a necessary part of web development, and MySQL, despite  
its warts, really gets it right. It is a hassle to have to jump  
through hoops (subselects, temp tables, inverted sort orders, ...) to  
do it. It is a shame because apparently the "standard" has no  
provision for this. But when you're putting data on the web, you  
simply must page records. And with MySQL it is a snap to augment any  
query to add paging, and it is fast.

So I am not surprised this is a major FAQ. I have the same problem  
with MSSQL as well. But my vague memory is that I can put ROW_NUMBER  
in a FROM clause in Sybase, and then use it in my WHERE, making it  
easy there as well.

We're pretty much back to square 1 on paging in Derby because we need  
to sort *before* we page (obviously). So now we're trying temporary  
tables (nuisance because our system uses configurable queries) and  
just spinning through the result set in Java.

Geoff