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

Dnia Fri, Sep 09, 2005 at 04:23:00PM +0400.424.r. (samokhvalov@gmail.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