I missed the ORDER BY clause... Here it goes:
select t1.id as id, t2.id as "id+1",
t1.thread as thread, t2.thread as "thread+1",
t1.info as info, t2.info as "info+1"
from test as t1, test as t2
where t2.id = ( select min(id) from test as t3 where t3.id > t1.id )
order by t1.id asc;
Also note that this query is much cheaper that the 'distinct on' query by
more than two orders on magnitude (217.86 vs. 98040.67):
postgres=# explain
postgres-# select
postgres-# distinct on (t1.id)
postgres-# t1.*, t2.*
postgres-# from
postgres-# test t1
postgres-# join test t2 on t2.id > t1.id
postgres-# order by t1.id asc, t2.id asc;
QUERY PLAN
------------------------------------------------------------------------------------------------
Unique (cost=95798.00..98040.67 rows=1160 width=80)
-> Sort (cost=95798.00..96919.33 rows=448533 width=80)
Sort Key: t1.id, t2.id
-> Nested Loop (cost=0.00..13827.29 rows=448533 width=80)
-> Seq Scan on test t1 (cost=0.00..21.60 rows=1160
width=40)
-> Index Scan using test_id_key on test t2
(cost=0.00..7.06rows=387 width=40)
Index Cond: (t2.id > t1.id)
(7 rows)
Time: 5.003 ms
postgres=# explain
postgres-# select t1.id as id, t2.id as "id+1",
postgres-# t1.thread as thread, t2.thread as "thread+1",
postgres-# t1.info as info, t2.info as "info+1"
postgres-# from test as t1, test as t2
postgres-# where t2.id = ( select min(id) from test as t3 where t3.id >
t1.id )
postgres-# order by t1.id asc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Sort (cost=214.96..217.86 rows=1160 width=80)
Sort Key: t1.id
-> Hash Join (cost=36.10..155.92 rows=1160 width=80)
Hash Cond: ((subplan) = t2.id)
-> Seq Scan on test t1 (cost=0.00..21.60 rows=1160 width=40)
-> Hash (cost=21.60..21.60 rows=1160 width=40)
-> Seq Scan on test t2 (cost=0.00..21.60 rows=1160
width=40)
SubPlan
-> Result (cost=0.13..0.14 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.13 rows=1 width=4)
-> Index Scan using test_id_key on test t3 (cost=
0.00..51.02 rows=387 width=4)
Index Cond: (id > $0)
Filter: (id IS NOT NULL)
(14 rows)
Time: 4.125 ms
Best regards,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
17°29'34.37"N 78°30'59.76"E - Hyderabad *
18°32'57.25"N 73°56'25.42"E - Pune
Sent from my BlackLaptop device
On 6/26/07, Gurjeet Singh <[EMAIL PROTECTED]> wrote:
Hi Rafal,
Just a note that this is not standard SQL... 'distinct on' is an
extension to SQL provided by postgres.
Following query utilizes the standard SQL to get the same results:
select t1.id as id, t2.id as "id+1",
t1.thread as thread, t2.thread as "thread+1",
t1.info as info, t2.info as "info+1"
from test as t1, test as t2
where t2.id = ( select min(id) from test as t3 where t3.id > t1.id);
HTH
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
17°29'34.37"N 78°30'59.76"E - Hyderabad *
18°32'57.25"N 73°56'25.42 "E - Pune
Sent from my BlackLaptop device
On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
>
> Marvelous! Thenx!
>
> -R
>
> On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote:
> > On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
> > Is there an SQL construct to get it?
> >
> > select
> > distinct on (t1.id)
> > t1.*, t2.*
> > from
> > test t1
> > join test t2 on t2.id > t1.id
> > order by t1.id asc, t2.id asc
> >
> > should do the trick.
> >
> > depesz
> >
> > --
> > http://www.depesz.com/ - nowy, lepszy depesz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
