Re: Using/Adressing a "row number" in a SELECT query

[Craig L Russell]

Hi Dan,












Best,

Craig

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

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.

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) {

    $counter++;
  }
  $counter = 0;
  while ($counter < $limit) {

    $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 <[EMAIL PROTECTED]> wrote:

db2_fetch_row($stmt); // simply advances result set pointer to the next row $rows[] = db2_fetch_array($stmt); // add the next row to the results array

Hi,


but can be used by the user to imbricate results.

So if you are willing to forego


Craig



On Feb 20, 2006, at 7:05 AM, Sylvain RICHET wrote:
(few days later...)

 [Michael said :]

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

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


$start AND $end;


from location-to-location when they are updated,

database.

 ... that's why Craig said :

SELECT * FROM(SELECT ..., [rowid] AS n FROM ... WHERE ... ) WHERE n BETWEEN For instance, this is possible with ORACLE, using its ROWNUM pseudo-column But certainly not the ROWID pseudo-column : because since rows can migrate ROWID should never be stored an never be counted on to be the same in any

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 :


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:

Is DERBY implementing a pseudo-columnn (let's say : "row") which authorize

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

Craig Russell

Architect, Sun Java Enterprise System
http://java.sun.com/products/jdo

408 276-5638 mailto:[EMAIL PROTECTED]

P.S. A good JDO? O, Gasp!

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

smime.p7s
Description: S/MIME cryptographic signature