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 (asf.osuosl.org: local policy)
From: Michael Segel <derby@segel.com>
Reply-To: derby@segel.com
Organization: MSCC 
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Using/Adressing a "row number" in a SELECT query
Date: Fri, 17 Feb 2006 09:59:06 -0600
User-Agent: KMail/1.8.2
References: <334d65b80602162347m6836e8c8uf0874aebd89f4697@mail.gmail.com>
In-Reply-To: <334d65b80602162347m6836e8c8uf0874aebd89f4697@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200602170959.06529.derby@segel.com>

On Friday 17 February 2006 1:47 am, Sylvain RICHET wrote:
> Hi everyone,
>
> In a selection statement, i would like to get blocks of records.
> Thus, i need to filter records by a "row number", directly at the SELECT
> level.
>
Hmmm, not sure this is going to give you the results that you want. 
You're implying that you want to select a record and its rowId. Then grab the 
first n row by using the rowId then fetch the next n rows at a later time 
again using rowId.

> It seems that the way to address a row number is not (SQL) standard.
> (different "proprietary" implementations)
>
Yup. The rowId really isn't part of the result set. Its more of some meta data 
if anything. Its also misleading and shouldn't be used anymore. (Unless you 
have a very good reason ... like writing your own index or something... ;-)

> In Oracle, there is the "rowid".
> In MySQL, the "LIMIT" clause can do it.
> In SQL Server, i think there is the "ROW_NUMBER() OVER..."
> In DB2 (on AS/400) , there is the "RRN" (Relative Record Number)...
>
Right.  And Informix has a rowId, however when you start to partion the DB 
tables, the rowId is no longer unique.

Again, for what you want, rowId is not going to work....

Imagine you have a table. You do a select on the table and you select rows 1, 
5, 10, 11,13,17 ... How does this help you when you want to fetch the first n 
rows?

I think you need to go back and rethink your design.

Based on your initial problem statement, rowId is not the best or even a good 
way of handling this.

Did you ever consider using a couple of prepare statements and scrolling 
cursors? ;-) (And if necessary a temp table...) 


But hey, what do I know?
I was a quasi-sales critter for the last 4 years... don't take my word for it. 
Take a fifth of scotch, and think about the problem you're trying to solve. 
You'll get it eventually. ;-)
-- 
--
Michael Segel
Principal 
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]