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 (athena.apache.org: local policy)
Reply-To: <msegel@segel.com>
From: "Derby Discussion" <derby-user@db.apache.org>
Sender: "Michael Segel" <msegel@segel.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: top N reporting with derby
Date: Thu, 15 May 2008 06:24:55 -0500
Organization: MSCC
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <E8451EDC-DCF9-4771-87FD-694ADC48CC19@sixfriedrice.com>
Thread-Index: Aci2PqJ33wuR1iOXSNSBavVuGGg9VgAP1LaA
Message-Id: <20080515113332.2A7BC1E267@dbrack01.segel.com>


Silly question... How does Derby handle temp tables?

As an example... IDS (Informix) would allow you to create temp tables on the
fly, index them so you could break the query down in to simpler queries.

You can't do this easily in Oracle because of how they create and maintain
temporary tables. (They really aren't temporary tables.) DB2? Something
similar to Oracle ...

So if you can create the temp table on the fly, just store your inner query
results to a temp table.

HTH

-Mike

> -----Original Message-----
> From: Six Fried Rice [mailto:technet@sixfriedrice.com]
> Sent: Wednesday, May 14, 2008 10:42 PM
> To: Derby Discussion
> Subject: Re: top N reporting with derby
> 
> On May 14, 2008, at 8:09 PM, Daniel Noll wrote:
> 
> > This is quite interesting.  Is there also a way to do this if you
> > need to join
> > to another table, such that it selects rows 100 to 200 of the first
> > table
> > before doing the join to the second?
> 
> Sorry for the confusion. My post was completely bogus. That's what I
> get for going from memory. Matt is completely correct: it seems you
> need a sub select to get a row number in a where clause, and you can't
> use order by in a sub select. Bummer really. I'm not sure *why* my
> query doesn't work, but it doesn't. I should say, though, that I'm a
> total derby newbie so it probably makes great sense.
> 
> Geoff