db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Using/Adressing a "row number" in a SELECT query
Date Wed, 22 Feb 2006 03:35:26 GMT
Hi Dan,

You are right that taking the requirements as stated, I don't see how  
Derby can do it. And I think others had the same opinion.

Looking at the requirement as "SELECT * FROM(SELECT ..., [rowid] AS n  
FROM ... WHERE ... ) WHERE n BETWEEN $start AND $end;" you can't do  
it. But in this case, the requirement seems self-defeating. Here's why:

The inner SELECT will do all the work to produce a result, including  
the joins, sort, merge, and then the outer SELECT throws away all of  
it except for the rows between the $start and $end. As an example, if  
you selected all the "Ma*" in the phone book and sorted on phone  
number, you would have thousands of rows to sort and then pick 20 of  
them. Not so good in general.

What Derby does to support paging is to allow you to collect the  
thousands of rows, sorted, on the server, and then use JDBC to page  
through the results. You only do the big inner select once and then  
go page through them.

The pseudo-PHP below seems to do the same thing that JDBC paging  
does, but IANAPHPE.

Best,

Craig

On Feb 20, 2006, at 12:34 PM, Dan Scott wrote:

> Except Sylvain's opening requirement states that he must have this
> directly at the SELECT level. He wants this on the fly as the result
> of a query, so to use the identity column approach he would need to
> dump the results of his query into a temporary table with an identity
> column, and then do the select with the corresponding "WHERE rowid > x
> AND rowid < y" clause to implement the equivalent of a LIMIT...OFFSET.
>
> But Derby doesn't support identity columns in temporary tables, so
> this rather complicated approach won't work.
>
> Sylvain, I think your only real option is to handle this outside the
> SELECT statement at the application layer. Worst-case scenario, you
> implement your pager function by calling fetch() until you reach _x_,
> then fetch() and keep rows until you reach _y_.
>
> A pseudo-implementation in PHP (minus error-checking etc) would work
> something like:
>
> function pager($stmt, $limit, $offset) {
>   $counter = 0;
>   $rows = array();
>   while ($counter < $offset) {
>     db2_fetch_row($stmt); // simply advances result set pointer to  
> the next row
>     $counter++;
>   }
>   $counter = 0;
>   while ($counter < $limit) {
>     $rows[] = db2_fetch_array($stmt); // add the next row to the  
> results array
>     $counter++;
>   }
> }
>
> And for a worst-case scenario, it turns out that this isn't really all
> that bad: there is almost no network traffic required to simply move
> the fetch() pointer ahead by a row when you're not actually retrieving
> a row.
>
> Dan
>
> On 2/20/06, Craig L Russell <Craig.Russell@sun.com> wrote:
>> Hi,
>>
>> I think that most of the databases you might want to use allow you  
>> to define
>> a column explicitly where the contents are managed by the database  
>> itself
>> but can be used by the user to imbricate results.
>>
>> So if you are willing to forego
>> a pseudo-columnn [sic] (let's say : "row") and instead use a real- 
>> column,
>> then I think the answer is yes. Derby has the "    MYROW INTEGER  
>> NOT NULL
>> GENERATED ALWAYS AS IDENTITY" construct that generates row values  
>> for you.
>>
>> Craig
>>
>>
>>
>> On Feb 20, 2006, at 7:05 AM, Sylvain RICHET wrote:
>> (few days later...)
>>
>>  [Michael said :]
>>>> 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.
>>
>>  The way i want to use the rowid would be in an imbricated select
>>
>>  SELECT * FROM(SELECT ..., [rowid] AS n FROM ... WHERE ... ) WHERE  
>> n BETWEEN
>> $start AND $end;
>>
>>  For instance, this is possible with ORACLE, using its ROWNUM  
>> pseudo-column
>>  But certainly not the ROWID  pseudo-column : because since rows  
>> can migrate
>> from location-to-location when they are updated,
>>  ROWID should never be stored an never be counted on to be the  
>> same in any
>> database.
>>
>>  ... that's why Craig said :
>>>> If you're using this for logging, and keeping track of which  
>>>> records you
>> have already processed, this technique might work.
>>>> Since the column is visible and won't change after insert, the same
>> technique can be used with other databases (e.g. use a sequence on
>> Oracle...)
>>
>>
>>  [Øystein said :]
>>>> I am bit confused about what you need a "row number" pointer for.
>>>> Oracle's RowID and MySql's LIMIT seems like quite different  
>>>> features.
>>  Sorry, i mentionned the ROWID instead of ROWNUM, which are both  
>> Oracle
>> pseudo-columns.
>>
>>
>>  So my initial question should have been :
>>
>>  Is DERBY implementing a pseudo-columnn (let's say : "row") which  
>> authorize
>> to do something like :
>>     SELECT * FROM(SELECT ..., [row] AS n FROM ... WHERE ... ) WHERE n
>> BETWEEN $start AND $end
>>  ... in order to get a paging system on the results ?
>>
>>
>> On 2/19/06, Craig L Russell <Craig.Russell@sun.com> wrote:
>>> Hi,
>>>
>>>
>>> You might consider using a column that the database automatically
>> increments for each inserted row. Then you could select ranges of  
>> this
>> column values.
>>>
>>>
>>> It's not clear from your description whether you know in advance  
>>> that you
>> want a certain range of rows that were inserted, or exactly what.
>>>
>>>
>>> If you're using this for logging, and keeping track of which  
>>> records you
>> have already processed, this technique might work. Since the  
>> column is
>> visible and won't change after insert, the same technique can be  
>> used with
>> other databases (e.g. use a sequence on Oracle...)
>>>
>>>
>>> Craig
>>>
>>>
>>>
>>> On Feb 16, 2006, at 11:47 PM, 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.
>>>
>>> It seems that the way to address a row number is not (SQL) standard.
>>> (different "proprietary" implementations)
>>>
>>> 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)...
>>>
>>> What about Derby database ?
>>> How is it implemented on this server ?
>>>
>>> I know i could use
>>>
>>> Thanks in advance.
>>>
>>>
>>> Craig Russell
>>> Architect, Sun Java Enterprise System
>> http://java.sun.com/products/jdo
>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>> P.S. A good JDO? O, Gasp!
>>>
>>>
>>
>>
>>
>>
>> Craig Russell
>>
>> Architect, Sun Java Enterprise System
>> http://java.sun.com/products/jdo
>>
>> 408 276-5638 mailto:Craig.Russell@sun.com
>>
>> P.S. A good JDO? O, Gasp!
>>
>>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message