Re: Special grouping on sorted data.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Nicolas Beuzeboc <nicolasb(at)norchemlab(dot)com>
Cc: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Special grouping on sorted data.
Date: 2008-09-24 02:25:23
Message-ID: 17094.1222223123@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Nicolas Beuzeboc <nicolasb(at)norchemlab(dot)com> writes:
> I which distinct on was more flexible, it's not happy when the order by
> set is different than the distinct on set.
> I would like to be able to write select distinct on (b,n) b,n,stamp from
> table where ... order by stamp;

Well, no, because it's defined to use the ORDER BY order to determine
which row is "first" within each DISTINCT ON group.

There is an easy workaround for this, which is to sort the rows again in
an outer select:

select * from
( select distinct on (b,n) b,n,stamp from
table where ... order by b,n,stamp ) ss
order by stamp;

regards, tom lane

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Oliveiros Cristina 2008-09-25 13:11:23 Re: exclusion query
Previous Message Nicolas Beuzeboc 2008-09-23 18:12:20 Re: Special grouping on sorted data.