Subject: Re: Simple SQL Question
From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Andras Kutrovics <n-drew@freemail.hu>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <418BB051.6070808@freemail.hu>
References: <418BB051.6070808@freemail.hu>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-5DW3MliWpvthJ5p5Omxl"
Organization: AK y Asociados S.R.L.
Message-Id: <1099678157.39887.15.camel@taz.oficina>
Mime-Version: 1.0
Date: Fri, 05 Nov 2004 15:09:20 -0300


--=-5DW3MliWpvthJ5p5Omxl
Content-Type: multipart/alternative; boundary="=-hoPsvHiTKF01gM1rzpfk"


--=-hoPsvHiTKF01gM1rzpfk
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

wouldn't it be easier using offset & limit?: you always select from the
table with an itemkey,location order by clause. You save the current
offset between requests, and for every request you re-execute the query
with a different offset.

If you still want to go with what you already have, you should keep the
lastItemKey and lastLocaltion values between requests, and your where
clause should be something like:
WHERE (itemKey=3D:lastItemKey AND location>:lastLocation) OR
(itemKey>:lastItemKey)

hope it helps.

On Fri, 2004-11-05 at 13:54, Andras Kutrovics wrote:

> Hi All!
>=20
> In one of my application, I would like to implement incremental
> fetching. The problem is that I would like to run it
> from a stateless application server, so I have to tell where
> to begin fetching data. It is simple for a table which has single column
> primary key, but causes difficulties (at least for me) in
> a more-column primary key..
>=20
> Let say I have a table wich has the primary key: itemkey,location
>=20
> table1
> ------
> itemkey
> location
> ...
> --------
>=20
>=20
> select * from table1 LIMIT x
>=20
> gives me the first x row of the result.
> After that, I save the last value, and next time, I adjust
> the query as
>=20
> select * from table1 where itemkey>:lastvalue LIMIT x
>=20
> that should be enough for an 'Item' table, but I have another
> column in the primary key.
>=20
> let say, I have the following in Table1:
>=20
> itemkey		location
> ------------------------
> 00001		00001
> 00001		00002
> ...
> 00005		00001
> 00005		00002
> 00005		00003 <--- lets say this is the last value
>=20
> next time i want to run a query, which starts from
>=20
> 00005		00004
> 00006		00005
>=20
> and so on..
>=20
> How can I specify that in sql?
> I dont want to use cursor:), I would like to do it in plain sql.
> (It it is possible).
>=20
> Thank you in advance
>=20
> Andras Kutrovics
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>=20

--=-hoPsvHiTKF01gM1rzpfk
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; CHARSET=3DUTF-8">
  <META NAME=3D"GENERATOR" CONTENT=3D"GtkHTML/3.0.10">
</HEAD>
<BODY>
wouldn't it be easier using offset &amp; limit?: you always select from the=
 table with an itemkey,location order by clause. You save the current offse=
t between requests, and for every request you re-execute the query with a d=
ifferent offset.<BR>
<BR>
If you still want to go with what you already have, you should keep the las=
tItemKey and lastLocaltion values between requests, and your where clause s=
hould be something like:<BR>
WHERE (itemKey=3D:lastItemKey AND location&gt;:lastLocation) OR (itemKey&gt=
;:lastItemKey)<BR>
<BR>
hope it helps.<BR>
<BR>
On Fri, 2004-11-05 at 13:54, Andras Kutrovics wrote:
<BLOCKQUOTE TYPE=3DCITE>
<PRE><FONT COLOR=3D"#737373"><I>Hi All!

In one of my application, I would like to implement incremental
fetching. The problem is that I would like to run it
from a stateless application server, so I have to tell where
to begin fetching data. It is simple for a table which has single column
primary key, but causes difficulties (at least for me) in
a more-column primary key..

Let say I have a table wich has the primary key: itemkey,location

table1
------
itemkey
location
...
--------


select * from table1 LIMIT x

gives me the first x row of the result.
After that, I save the last value, and next time, I adjust
the query as

select * from table1 where itemkey&gt;:lastvalue LIMIT x

that should be enough for an 'Item' table, but I have another
column in the primary key.

let say, I have the following in Table1:

itemkey		location
------------------------
00001		00001
00001		00002
...
00005		00001
00005		00002
00005		00003 &lt;--- lets say this is the last value

next time i want to run a query, which starts from

00005		00004
00006		00005

and so on..

How can I specify that in sql?
I dont want to use cursor:), I would like to do it in plain sql.
(It it is possible).

Thank you in advance

Andras Kutrovics

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
</I></FONT></PRE>
</BLOCKQUOTE>
</BODY>
</HTML>

--=-hoPsvHiTKF01gM1rzpfk--

--=-5DW3MliWpvthJ5p5Omxl
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (FreeBSD)

iD8DBQBBi8HN21dVnhLsBV0RAirGAJ0bT2HYGlbSajugAicEO7StJhdUewCeKH2r
DRfVvr/u4hRFMOGYQGyiY1U=
=QZSR
-----END PGP SIGNATURE-----

--=-5DW3MliWpvthJ5p5Omxl--