Date: Tue, 13 Sep 2005 16:04:03 +0200
From: Marcin Piotr Grondecki <ojciec@postgres.pl>
To: pgsql-sql@postgresql.org
Subject: Re: SELECT: retrieve only 2 rows next to known row
Message-ID: <20050913140403.GB18531@localhost.localdomain>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <e431ff4c050909052360490fc2@mail.gmail.com>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="K8nIJk4ghYZn606h"
Content-Disposition: inline
In-Reply-To: <e431ff4c050909052360490fc2@mail.gmail.com>
User-Agent: mutt-ng/devel (Linux)


--K8nIJk4ghYZn606h
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Dnia Fri, Sep 09, 2005 at 04:23:00PM +0400.424.r. (samokhvalov@gmail.com), =
Nikolay Samokhvalov napisal(a):
> Hi,
>=20
> My knowledge of PostgreSQL's SQL is not good, but I know ISO/ANSI
> SQL:2003 (basics) quite well.
>=20
> I've encountered with following task. I have one SELECT statement with
> ORDER BY clause; and know, that result set for this SELECT contains
> row with ID =3D 1000 (just for example).
> I don't know the position of this row in result set, but I want to
> retrieve 2 rows that are next to this  one.
>=20
> First of all, I don't want (cannot) write PL/pgSQL function.
>=20
> So, I should use CREATE TEMP SEQUENCE to associate all rows in result
> set with their order numbers (in MySQL. for example, I would use
> temporary variable num in SELECT: something like 'select @num :=3D @num
> + 1', but here I cannot, can't I?)
>=20
> Then, as I know, PostgreSQL doesn't support standard statement WITH,
> that probaby would help me with this task.
>=20
> Any ways to solve this problem? Is it possible to make only one query?
> (at least with one row in result set - e.g., with the row _following_
> after my one)
>=20
> I'd appreciate any help.
I've created some model of your problem.
CREATE TABLE "foo" ("id" serial, "val" text); -- (this "model" is named "fo=
o", as you see :]).
Then I've inserted some values; 'SELECT * FROM "foo" ORDER BY ("val")' give=
s now:
 id | val
----+-----
  4 | a
  2 | b
  3 | c
  1 | d
  5 | e
  7 | f
  6 | g
Now we'd like to "find" row where val=3D'd' and this row neighbours.
This question results with our "center" row and next one:
	SELECT * FROM "foo" WHERE ("val">=3D'd') ORDER BY ("val") LIMIT 2;
Previous row:
	SELECT * FROM "foo" WHERE ("val"<'d') ORDER BY ("val") DESC LIMIT 1;
Is it expected result in task similar to yours?
Ah, 'Is it possible to make only one query?'.
Yep, by "unioning" two given questions:
	(SELECT * FROM "foo" WHERE ("val">=3D'd') ORDER BY ("val") LIMIT 2) UNION =
ALL (SELECT * FROM "foo" WHERE ("val"<'d') ORDER BY ("val") DESC LIMIT 1) O=
RDER BY ("val");
Effect:
 id | val
----+-----
  3 | c
  1 | d
  5 | e
As desired.
Now we can change field/s to watch values for (we were looking into "val", =
now we'd like to do same work on "id").=20
	(SELECT * FROM "foo" WHERE ("id">=3D'5') ORDER BY ("id") LIMIT 2) UNION AL=
L (SELECT * FROM "foo" WHERE ("id"<'5') ORDER BY "id" DESC LIMIT 1) ORDER B=
Y ("id");
It gives:=20
 id | val
----+-----
  4 | a
  5 | e
  6 | g
Once again result as expected in our dreams! :]]]

Change table into desired one, change ordering into desired one and... your=
 problem'll be solved, I hope... :]?

regards
--=20
Marcin Piotr Grondecki

--K8nIJk4ghYZn606h
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFDJtxTCAdl/NTXOsERApBPAJ99ov5HYEGBr7YLhDg0wnzDHrB1IQCdEV4X
VXOOjgaYQDty4KXcOAvfsf0=
=Q24m
-----END PGP SIGNATURE-----

--K8nIJk4ghYZn606h--