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

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

From: stephen(at)thunkit(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: 'distinct on' and 'order by' conflicts of interest
Date: 2004-12-31 16:48:21
Message-ID: 3068.67.21.238.59.1104511701.squirrel@mail.thunkit.com (view raw or flat)
Thread:
Lists: pgsql-general
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 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?

Responses

pgsql-general by date

Next:From: Michael FuhrDate: 2004-12-31 17:36:36
Subject: Re: 'distinct on' and 'order by' conflicts of interest
Previous:From: Jaime CasanovaDate: 2004-12-31 16:07:29
Subject: Re: [PATCHES] reqd patch

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