Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 85419 invoked from network); 17 Feb 2006 13:42:14 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 17 Feb 2006 13:42:14 -0000 Received: (qmail 1531 invoked by uid 500); 17 Feb 2006 13:42:12 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 1493 invoked by uid 500); 17 Feb 2006 13:42:12 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 1470 invoked by uid 99); 17 Feb 2006 13:42:12 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 17 Feb 2006 05:42:12 -0800 X-ASF-Spam-Status: No, hits=1.3 required=10.0 tests=HTML_MESSAGE,RCVD_IN_BL_SPAMCOP_NET,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of derbysr@gmail.com designates 66.249.92.202 as permitted sender) Received: from [66.249.92.202] (HELO uproxy.gmail.com) (66.249.92.202) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 17 Feb 2006 05:42:11 -0800 Received: by uproxy.gmail.com with SMTP id m2so264992uge for ; Fri, 17 Feb 2006 05:41:50 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=KRPkSJaqqdqFn8yEOt0cLSACKigva497XMh4+me4KKrMb84nH1S8rbyBoWyQTKTnQeQ87qxO0R0HsNTnsLZ4Om7joNAWVJWuvSnAxlw+cxyyjP/7yPaLLMJgSQ6cW4zEjtP1Pwjj7vP+6OC1b4okjcr2ySrmN3DkbPv5wBLz62o= Received: by 10.66.241.5 with SMTP id o5mr720997ugh; Fri, 17 Feb 2006 05:41:47 -0800 (PST) Received: by 10.66.242.7 with HTTP; Fri, 17 Feb 2006 05:41:46 -0800 (PST) Message-ID: <334d65b80602170541g40aecd91r55fe19b3d4dfb59d@mail.gmail.com> Date: Fri, 17 Feb 2006 14:41:46 +0100 From: Sylvain RICHET To: Derby Discussion Subject: Re: Using/Adressing a "row number" in a SELECT query In-Reply-To: <20060217132528.GA9254@localhost.localdomain> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_305_18745645.1140183706623" References: <334d65b80602162347m6836e8c8uf0874aebd89f4697@mail.gmail.com> <43F590B3.1000400@gmail.com> <334d65b80602170255t2371fdd8refa09bbc721ea7e3@mail.gmail.com> <20060217132528.GA9254@localhost.localdomain> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_305_18745645.1140183706623 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 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 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=3D > =3DtYlq > -----END PGP SIGNATURE----- > > > ------=_Part_305_18745645.1140183706623 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline OK Bernt, thanks.

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



On 2/17/06, Bernt M. Johnsen <B= ernt.Johnsen@sun.com> 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 wil= l
not affect the result.

setMaxRows will limit the number of rows= in the resulting
resultset. And any decent JDBC-driver will limit the n= umber of rows
"loaded" from the database (in client/server mode, that is).<= br>
>
> However, the datas persisted in my DERBY database conce= rn logging. It means
> that i would have to get thousands of records.
>
> On 2/17/06, Legolas Woodland <legolas.w@gmail.com> wrote:
> >
> > = Sylvain RICHET wrote:
> >
> > Hi everyone,
> >
> > In a selection statement, i would like to get blocks of recor= ds.
> > Thus, i need to filter records by a "row number"= , directly at the SELECT
> > level.
> >
> > It s= eems that the way to address a row number is not (SQL) standard.
> > (different "proprietary" implementations)
> &= gt;
> > 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 R= ecord Number)...
> >
> > What about Derby database ?
&= gt; > How is it implemented on this server ?
> >
> > I= know i could use
> >
> > Thanks in advance.
> >
> > Der= by sql does not has any facilities for limiting number of rows in
> &= gt; select statement.
> > you can just limit the fetch size in JDB= C resultSets.
> >
> >

--
Bernt Marius Johnsen, Database Tech= nology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Micros= ystems, Trondheim, Norway


-----BEGIN PGP SIGNATURE-----
Versi= on: GnuPG=20 v1.4.1 (GNU/Linux)

iD8DBQFD9c7IlFBD9TXBAPARAvSNAKC1tC9EW6HP+9VXPzivC= uy8Zxg4iwCffubD
2n2X6L/xi6B+beWfnVYMCQQ=3D
=3DtYlq
-----END PGP SI= GNATURE-----



------=_Part_305_18745645.1140183706623--