db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Re: Using/Adressing a "row number" in a SELECT query
Date Fri, 17 Feb 2006 15:59:06 GMT
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 

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
Michael Segel Consulting Corp.
(312) 952-8175 [mobile]

View raw message