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

Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> writes:
> 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.

In general there is no such thing as "the row next to this one". SQL
treats all data sets as unordered, up until the point where you do an
explicit ORDER BY for display purposes. So your request is really
meaningless unless you phrase it as "I want the rows that come
immediately before and after this one in such-an-such an ordering".

Once you do that, there is more than one way to solve the problem.
For example, if the ordering you care about is on an indexed field,
you could do something like

SELECT * FROM tab
WHERE foo > (SELECT foo FROM tab WHERE condition-to-select-reference-row)
ORDER BY foo
LIMIT 1

to get the following row, and

SELECT * FROM tab
WHERE foo < (SELECT foo FROM tab WHERE condition-to-select-reference-row)
ORDER BY foo DESC
LIMIT 1

to get the prior one (and if you really want just one query result,
put these together with UNION ALL).

Other solutions that come to mind involve cursors. You haven't told us
enough about either the required ordering or the nature of the condition
that defines "this row" to really say much about the best solution.

regards, tom lane