| 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 <[EMAIL PROTECTED]> 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 P.S. A good JDO? O, Gasp!
|
smime.p7s