Thread: SELECT: retrieve only 2 rows next to known row
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. -- Best regards, Nikolay
On Fri, Sep 09, 2005 at 16:23:00 +0400, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote: > > 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. > > > 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) You could search for the following and preceding rows of the row with an id of 1000 by using the comparison field(s) of the row in a where clause. You can use limit 1 for each of these two subselects and union the results. You may need to add the ID to the order by clause if your current ordering does not produce a unique order.
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
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