pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT: retrieve only 2 rows next to known row
4+ messages / 4 participants
[nested] [flat]
* SELECT: retrieve only 2 rows next to known row
@ 2005-09-09 12:23 Nikolay Samokhvalov <samokhvalov@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Nikolay Samokhvalov @ 2005-09-09 12:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT: retrieve only 2 rows next to known row
@ 2005-09-09 12:38 Bruno Wolff III <bruno@wolff.to>
parent: Nikolay Samokhvalov <samokhvalov@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2005-09-09 12:38 UTC (permalink / raw)
To: nikolay@samokhvalov.com; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT: retrieve only 2 rows next to known row
@ 2005-09-09 14:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nikolay Samokhvalov <samokhvalov@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-09-09 14:37 UTC (permalink / raw)
To: nikolay@samokhvalov.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT: retrieve only 2 rows next to known row
@ 2005-09-13 14:04 Marcin Piotr Grondecki <ojciec@postgres.pl>
parent: Nikolay Samokhvalov <samokhvalov@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Marcin Piotr Grondecki @ 2005-09-13 14:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-09-13 14:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-09 12:23 SELECT: retrieve only 2 rows next to known row Nikolay Samokhvalov <samokhvalov@gmail.com>
2005-09-09 12:38 ` Bruno Wolff III <bruno@wolff.to>
2005-09-09 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-13 14:04 ` Marcin Piotr Grondecki <ojciec@postgres.pl>
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