db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dan Scott" <deni...@gmail.com>
Subject Re: Using/Adressing a "row number" in a SELECT query
Date Mon, 20 Feb 2006 20:34:20 GMT
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!
>
>

Mime
View raw message