From: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | "Rafal Pietrak" <rafal(at)zorro(dot)isa-geek(dot)com> |
Cc: | "hubert depesz lubaczewski" <depesz(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: a JOIN on same table, but 'slided over' |
Date: | 2007-06-26 12:35:46 |
Message-ID: | 65937bea0706260535p2cbc7ddak73fac81bcb815ffb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ 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 <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
>
> Marvelous! Thenx!
>
> -R
>
> On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote:
> > On 6/26/07, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> 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/
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2007-06-26 12:56:30 | Re: yet another simple SQL question |
Previous Message | Rafal Pietrak | 2007-06-26 12:28:27 | Re: a JOIN on same table, but 'slided over' |