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 
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]

Mime
View raw message