Search Postgresql Archives

Re: a JOIN on same table, but 'slided over'

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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[.singh]@EnterpriseDB.com
singh.gurjeet@{ 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@xxxxxxxxxxxxxxxxxx> wrote:
Marvelous! Thenx!

-R

On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote:
> On 6/26/07, Rafal Pietrak <rafal@xxxxxxxxxxxxxxxxxx> 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/


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux