From: | Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | a JOIN on same table, but 'slided over' |
Date: | 2007-06-26 07:10:34 |
Message-ID: | 1182841834.28091.34.camel@zorro.isa-geek.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I understand, that this is 'general SQL' question rather then 'general
postgres'. But may be someone here could help me with it anyways.
I have a *single* table:
CREATE TABLE test (id int not null unique, thread int not null, info
text);
The ID, although unique, is not continues. A sample query:
----------------------------------------
SELECT * from test;
id | thread | info
----+--------+------
2 | 763 | A
3 | 764 | B
6 | 5 | C
8 | 88946 | Cats
9 | 69315 | Eifel
10 | 96379 | G
14 | 23927 | test 1
16 | 16529 | test 2
17 | 634 | test 3
20 | 63930 | batman
(10 rows)
-----------------------------------------
Now, I'd like to make a JOIN-ed query of that table with itself, so that
I'd get rows paiwise: every row containing data from *two* rows of the
original TEST table so, that those data come from rows of consequtive
ID's - not neceserly (depending on the TEST table contents) continuesly
consequtive. Like:
SELECT * from view_of_test;
id | id+X | thread | thread+X | info | info+X
----+------+--------+----------+-------+---------
2 | 3 | 763 | 764 | A | B
3 | 6 | 764 | 5 | B | C
6 | 8 | 5 | 88946 | C | Cats
8 | 9 | 88946 | 69315 | Cats | Eifel
9 | 10 | 69315 | 96379 | Eifel | G
-------------------------------------------------
Is there an SQL construct to get it?
I'd apreciate any hints or sugestions.
-R
From | Date | Subject | |
---|---|---|---|
Next Message | Bart Degryse | 2007-06-26 07:14:17 | Re: yet another simple SQL question |
Previous Message | John Summerfield | 2007-06-26 06:40:11 | Re: yet another simple SQL question |