db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sylvain RICHET" <derb...@gmail.com>
Subject Re: Using/Adressing a "row number" in a SELECT query
Date Mon, 20 Feb 2006 15:05:46 GMT
(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 <Craig.Russell@sun.com>
>
> P.S. A good JDO? O, Gasp!
>
>
>

Mime
View raw message