From: Tom Lane <tgl@sss.pgh.pa.us>
To: nikolay@samokhvalov.com
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT: retrieve only 2 rows next to known row
Date: Fri, 09 Sep 2005 10:37:00 -0400
Message-ID: <10447.1126276620@sss.pgh.pa.us> (raw)
In-Reply-To: <e431ff4c050909052360490fc2@mail.gmail.com>
References: <e431ff4c050909052360490fc2@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, nikolay@samokhvalov.com
Subject: Re: SELECT: retrieve only 2 rows next to known row
In-Reply-To: <10447.1126276620@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox