To: nikolay@samokhvalov.com
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT: retrieve only 2 rows next to known row 
In-reply-to: <e431ff4c050909052360490fc2@mail.gmail.com> 
References: <e431ff4c050909052360490fc2@mail.gmail.com>
Comments: In-reply-to Nikolay Samokhvalov <samokhvalov@gmail.com>
	message dated "Fri, 09 Sep 2005 16:23:00 +0400"
Date: Fri, 09 Sep 2005 10:37:00 -0400
Message-ID: <10447.1126276620@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Nikolay Samokhvalov <samokhvalov@gmail.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