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

[J . English]

Craig L Russell wrote:

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.


How? Can you explain further, please?

(This thread is related to a number of similar questions I've asked
in the recent past, and I'm still no closer to a workable solution,
but this sounds potentially helpful.)

-
 John English  | mailto:[EMAIL PROTECTED]
 Senior Lecturer   | http://www.it.bton.ac.uk/staff/je
 School of Computing & MIS | ** NON-PROFIT CD FOR CS STUDENTS **
 University of Brighton|-- see http://burks.bton.ac.uk
-

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

[Craig L Russell]

Hi Dan,

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.


Best,

Craig

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


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

Hi,


You might consider using a column that the database automatically
increments for each inserted row. Then you could select ranges o

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

[Dan Scott]

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 <[EMAIL PROTECTED]> 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 <[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 A

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

[Craig L Russell]

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.CraigOn 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...)CraigOn 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!  

smime.p7s
Description: S/MIME cryptographic signature

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

[Sylvain RICHET]

(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...)CraigOn 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!  

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

[Craig L Russell]

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...)CraigOn 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!  

smime.p7s
Description: S/MIME cryptographic signature

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

[Michael Segel]

On Friday 17 February 2006 1:47 am, 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.
>
Hmmm, not sure this is going to give you the results that you want. 
You're implying that you want to select a record and its rowId. Then grab the 
first n row by using the rowId then fetch the next n rows at a later time 
again using rowId.

> It seems that the way to address a row number is not (SQL) standard.
> (different "proprietary" implementations)
>
Yup. The rowId really isn't part of the result set. Its more of some meta data 
if anything. Its also misleading and shouldn't be used anymore. (Unless you 
have a very good reason ... like writing your own index or something... ;-)

> 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)...
>
Right.  And Informix has a rowId, however when you start to partion the DB 
tables, the rowId is no longer unique.

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.

Based on your initial problem statement, rowId is not the best or even a good 
way of handling this.

Did you ever consider using a couple of prepare statements and scrolling 
cursors? ;-) (And if necessary a temp table...) 


But hey, what do I know?
I was a quasi-sales critter for the last 4 years... don't take my word for it. 
Take a fifth of scotch, and think about the problem you're trying to solve. 
You'll get it eventually. ;-)
-- 
--
Michael Segel
Principal 
Michael Segel Consulting Corp.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]

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

[Oystein Grovlen - Sun Norway]

Sylvain RICHET wrote:

OK Bernt, thanks.

I have already tested these methods.


It seems like these methods does not satisfy your needs.  Can you tell 
us why?


Maybe anything to add about the lack of "row number" pointer at SQL 
level ? ...




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.


--
Øystein





On 2/17/06, *Bernt M. Johnsen* <[EMAIL PROTECTED] 
> wrote:


  Sylvain RICHET wrote (2006-02-17 11:55:37):
 > Thanks Legolas,
 >
 > ... but limiting the fetch size by JDBC API supported methods
(setFetchSize)
 > implies that i have already loaded ALL records from database, no ?

setFetchSize is an optimization hint to the driver/database and will
not affect the result.

setMaxRows will limit the number of rows in the resulting
resultset. And any decent JDBC-driver will limit the number of rows
"loaded" from the database (in client/server mode, that is).

 >
 > However, the datas persisted in my DERBY database concern
logging. It means
 > that i would have to get thousands of records.
 >
 > On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]
> wrote:
 > >
 > > 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.
 > >
 > > Derby sql does not has any facilities for limiting number of
rows in
 > > select statement.
 > > you can just limit the fetch size in JDBC resultSets.
 > >
 > >

--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFD9c7IlFBD9TXBAPARAvSNAKC1tC9EW6HP+9VXPzivCuy8Zxg4iwCffubD
2n2X6L/xi6B+beWfnVYMCQQ=
=tYlq
-END PGP SIGNATURE-






--
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Technology Group
Trondheim, Norway

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

[Sylvain RICHET]

OK Bernt, thanks.

I have already tested these methods.
Maybe anything to add about the lack of "row number" pointer at SQL level ? ...

On 2/17/06, Bernt M. Johnsen <[EMAIL PROTECTED]> wrote:
 Sylvain RICHET wrote (2006-02-17 11:55:37):> Thanks Legolas,>> ... but limiting the fetch size by JDBC API supported methods (setFetchSize)> implies that i have already loaded ALL records from database, no ?
setFetchSize is an optimization hint to the driver/database and willnot affect the result.setMaxRows will limit the number of rows in the resultingresultset. And any decent JDBC-driver will limit the number of rows
"loaded" from the database (in client/server mode, that is).>> However, the datas persisted in my DERBY database concern logging. It means> that i would have to get thousands of records.
>> On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]> wrote:> >> > 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.> >> > Derby sql does not has any facilities for limiting number of rows in> > select statement.> > you can just limit the fetch size in JDBC resultSets.
> >> >--Bernt Marius Johnsen, Database Technology Group,Staff Engineer, Technical Lead Derby/Java DBSun Microsystems, Trondheim, Norway-BEGIN PGP SIGNATURE-Version: GnuPG 
v1.4.1 (GNU/Linux)iD8DBQFD9c7IlFBD9TXBAPARAvSNAKC1tC9EW6HP+9VXPzivCuy8Zxg4iwCffubD2n2X6L/xi6B+beWfnVYMCQQ==tYlq-END PGP SIGNATURE-

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

[Bernt M. Johnsen]

 Sylvain RICHET wrote (2006-02-17 11:55:37):
> Thanks Legolas,
> 
> ... but limiting the fetch size by JDBC API supported methods (setFetchSize)
> implies that i have already loaded ALL records from database, no ?

setFetchSize is an optimization hint to the driver/database and will
not affect the result.

setMaxRows will limit the number of rows in the resulting
resultset. And any decent JDBC-driver will limit the number of rows
"loaded" from the database (in client/server mode, that is).

> 
> However, the datas persisted in my DERBY database concern logging. It means
> that i would have to get thousands of records.
> 
> On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]> wrote:
> >
> > 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.
> >
> > Derby sql does not has any facilities for limiting number of rows in
> > select statement.
> > you can just limit the fetch size in JDBC resultSets.
> >
> >

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway


signature.asc
Description: Digital signature

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

[Legolas Woodland]

Sylvain RICHET wrote:
Thanks Legolas, for your explanations.
  

You are welcome. 
it was my first time that i could help a member in mailing list
so far i just request for help in past days.
now i am happy ;-)

  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]> wrote:
   Sylvain RICHET wrote:
Though it is astonishing there is no way to address/access
the "row
number" on DERBY , whereas it's possible with other database systems...
  
Have you ever heard about any workaround ?

I dont think that you can find a workaround for this.
if you file a Jira entry i will vote for it.
Thanks


  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]
  > wrote:
  
Sylvain RICHET wrote:
Thanks Legolas,
  
... but limiting the fetch size by JDBC API supported methods
(setFetchSize) implies that i have already loaded ALL records from
database, no ?
  

I asked about this feature  sometimes before.
it is not present in current derby version , but you can vote on its
jira entry or create a jira entry for it.

 
However, the datas persisted in my DERBY database concern
logging. It means that i would have to get thousands of records.
  
  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]
  > wrote:
  
 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. 

Derby sql does not has any facilities for limiting number of rows in
select statement.
you can just limit the fetch size in JDBC resultSets.

  
  
  



  
  
  



  
  
  

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

[Sylvain RICHET]

Thanks Legolas, for your explanations.On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]> wrote:



  


Sylvain RICHET wrote:
Though it is astonishing there is no way to address/access
the "row
number" on DERBY , whereas it's possible with other database systems...
  
Have you ever heard about any workaround ?

I dont think that you can find a workaround for this.
if you file a Jira entry i will vote for it.
Thanks

  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]
> wrote:
   Sylvain RICHET wrote:
Thanks Legolas,
  
... but limiting the fetch size by JDBC API supported methods
(setFetchSize) implies that i have already loaded ALL records from
database, no ?
  

I asked about this feature  sometimes before.
it is not present in current derby version , but you can vote on its
jira entry or create a jira entry for it.

 
However, the datas persisted in my DERBY database concern
logging. It means that i would have to get thousands of records.
  
  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]
  > wrote:
  
 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. 

Derby sql does not has any facilities for limiting number of rows in
select statement.
you can just limit the fetch size in JDBC resultSets.

  
  
  



  
  
  

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

[Legolas Woodland]

Sylvain RICHET wrote:
Though it is astonishing there is no way to address/access
the "row
number" on DERBY , whereas it's possible with other database systems...
  
Have you ever heard about any workaround ?

I dont think that you can find a workaround for this.
if you file a Jira entry i will vote for it.
Thanks

  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]> wrote:
   Sylvain RICHET wrote:
Thanks Legolas,
  
... but limiting the fetch size by JDBC API supported methods
(setFetchSize) implies that i have already loaded ALL records from
database, no ?
  

I asked about this feature  sometimes before.
it is not present in current derby version , but you can vote on its
jira entry or create a jira entry for it.

 
However, the datas persisted in my DERBY database concern
logging. It means that i would have to get thousands of records.
  
  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]
  > wrote:
  
 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. 

Derby sql does not has any facilities for limiting number of rows in
select statement.
you can just limit the fetch size in JDBC resultSets.

  
  
  



  
  
  

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

[Sylvain RICHET]

Though it is astonishing there is no way to address/access the "row
number" on DERBY , whereas it's possible with other database systems...
Have you ever heard about any workaround ?
On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]> wrote:



  


Sylvain RICHET wrote:
Thanks Legolas,
  
... but limiting the fetch size by JDBC API supported methods
(setFetchSize) implies that i have already loaded ALL records from
database, no ?
  

I asked about this feature  sometimes before.
it is not present in current derby version , but you can vote on its
jira entry or create a jira entry for it.
 
However, the datas persisted in my DERBY database concern
logging. It means that i would have to get thousands of records.
  
  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]
> wrote:
  
 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.


Derby sql does not has any facilities for limiting number of rows in
select statement.
you can just limit the fetch size in JDBC resultSets.

  
  
  

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

[Legolas Woodland]

Sylvain RICHET wrote:
Thanks Legolas,
  
... but limiting the fetch size by JDBC API supported methods
(setFetchSize) implies that i have already loaded ALL records from
database, no ?
  

I asked about this feature  sometimes before.
it is not present in current derby version , but you can vote on its
jira entry or create a jira entry for it.
 
However, the datas persisted in my DERBY database concern
logging. It means that i would have to get thousands of records.
  
  On 2/17/06, Legolas Woodland <[EMAIL PROTECTED]> wrote:
  
 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.


Derby sql does not has any facilities for limiting number of rows in
select statement.
you can just limit the fetch size in JDBC resultSets.

  
  
  

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

[Sylvain RICHET]

Thanks Legolas,

... but limiting the fetch size by JDBC API supported methods
(setFetchSize) implies that i have already loaded ALL records from
database, no ?

However, the datas persisted in my DERBY database concern logging. It means that i would have to get thousands of records.On 2/17/06, Legolas Woodland
 <[EMAIL PROTECTED]> wrote:



  


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.

Derby sql does not has any facilities for limiting number of rows in
select statement.
you can just limit the fetch size in JDBC resultSets.

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

[Legolas Woodland]

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.

Derby sql does not has any facilities for limiting number of rows in
select statement.
you can just limit the fetch size in JDBC resultSets.