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 Mon, 20 Feb 2006 20:10:59 GMT
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!


Mime
View raw message