Skip site navigation (1) Skip section navigation (2)

Re: 'distinct on' and 'order by' conflicts of interest

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: stephen(at)thunkit(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: 'distinct on' and 'order by' conflicts of interest
Date: 2004-12-31 17:43:26
Message-ID: 20041231093807.U581@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, 31 Dec 2004 stephen(at)thunkit(dot)com wrote:

> It has come up several times on the various postgresql lists that in order
> to get around the requirement of DISTINCT ON parameters matching the first
> ORDER BY parameters, wrap the distinct query in a new 'order by' query:
>
> select * from (select distinct on (a) a,b,c from foo order by a) order by c
>
> however, this will not work when ordering by 'a' will put the wrong record
> first, making it choose the wrong record via distinct on.  The 'order by
> c' superquery no longer has the correct recordset to sort.

I'm not entirely sure from the above which record you want to get from the
distinct on. In general for something like the above, in the subselect you
order by the distinct on column(s) and then the column(s) which control
the which row you want (for example order by a,c). Then you can use the
outer order by to change the ordering to no longer use the distinct on
columns.

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-12-31 18:44:13
Subject: Re: pg_dump and pgpool
Previous:From: Joshua D. DrakeDate: 2004-12-31 17:40:27
Subject: Re: Large Objects

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group