Re: SELECT: retrieve only 2 rows next to known row

Dnia Fri, Sep 09, 2005 at 04:23:00PM +0400.424.r. (samokhvalov(at)gmail(dot)com), Nikolay Samokhvalov napisal(a):
> Hi,
>
> My knowledge of PostgreSQL's SQL is not good, but I know ISO/ANSI
> SQL:2003 (basics) quite well.
>
> 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 = 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.
>
> First of all, I don't want (cannot) write PL/pgSQL function.
>
> 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 := @num
> + 1', but here I cannot, can't I?)
>
> Then, as I know, PostgreSQL doesn't support standard statement WITH,
> that probaby would help me with this task.
>
> 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)
>
> I'd appreciate any help.
I've created some model of your problem.
CREATE TABLE "foo" ("id" serial, "val" text); -- (this "model" is named "foo", as you see :]).
Then I've inserted some values; 'SELECT * FROM "foo" ORDER BY ("val")' gives 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='d' and this row neighbours.
This question results with our "center" row and next one:
SELECT * FROM "foo" WHERE ("val">='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">='d') ORDER BY ("val") LIMIT 2) UNION ALL (SELECT * FROM "foo" WHERE ("val"<'d') ORDER BY ("val") DESC LIMIT 1) ORDER 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").
(SELECT * FROM "foo" WHERE ("id">='5') ORDER BY ("id") LIMIT 2) UNION ALL (SELECT * FROM "foo" WHERE ("id"<'5') ORDER BY "id" DESC LIMIT 1) ORDER BY ("id");
It gives:
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
--
Marcin Piotr Grondecki