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

On Fri, Dec 31, 2004 at 10:48:21AM -0600, 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.

You should be ordering by more than just "a" in the DISTINCT ON
query. SELECT DISTINCT ON (a) gives you the first row for each "a"
value; if you don't specify an order for additional fields then
you'll get some arbitrary row. See the weather_reports example in
the documentation for SELECT in the "SQL Commands" part of the
documentation.

> I cannot figure out how to have postgresql first sort the results (on
> something other than the 'distinct on' parameters) and then do a recordset
> culling by only part of the record. Does anyone have the syntax for this?

Define the problem, not how you think it should be solved. What
are you trying to do? If you can't get the query to work, then
please post SQL statements to create and populate a table and
describe the query results you'd like to see.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/