Re: No sort with except

Hi Reto

You are right to assume that you're query is ordering the second select
and not the whole query.  To order the query as a whole it in
parentheses and put the ORDER BY at the end:

(
SELECT foo FROM X
EXCEPT
SELECT foo FROM Y
) ORDER BY foo;

Hope this helps


On 01/03/2012 08:56, reto.buchli@wsl.ch wrote:
> 
> pgsql-sql-owner@postgresql.org schrieb am 01.03.2012 09:16:53:
> 
>> From: Frank Lanitz <frank@frank.uvena.de>
>> To: pgsql-sql@postgresql.org,
>> Date: 01.03.2012 09:16
>> Subject: Re: [SQL] No sort with except
>> Sent by: pgsql-sql-owner@postgresql.org
>>
>> Am 01.03.2012 09:13, schrieb reto.buchli@wsl.ch:
>> > Dear all,
>> >
>> > When I run the following SQL with PostgreSQL 9.1:
>> >
>> > --
>> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat,
> status
>> >   FROM person
>> >        
>> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
>> >         AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
>> >
>> > ORDER BY pernr, eindt DESC;
>> > --
>> >
>> > it works. I get the most recent persons, even if one came back within
>> > this time range.
>> >
>> > But if i do this:
>> >
>> > ---
>> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat,
> status
>> >   FROM person
>> >        
>> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
>> >         AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
>> > EXCEPT        
>> >
>> > SELECT DISTINCT ON (pernr) pernr,  vorna, nachn, eindt, ausdt, updat,
>> > status
>> >   FROM person
>> >        RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid
> = 10
>> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
>> >         AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
>> > ORDER BY pernr, eindt DESC;
>> > ---
>> >
>> > In this case the ORDER BY does not work: I will get the same person
>> > data, either with DESC as with ASC, even when this should change.
>> >
>> > Does anyone have an explanation for this?
>>
>>
>> Don't you sort just the part at EXCEPT?
>>
>> Cheers,
>> Frank
>>
>>
> Hi Frank
> This may be. But as I understand, this will sort the result set. I'm
> also not able to place ORDER BY before the EXCEPT.
> 
> Am I wrong?
> 
> Cheers,
> Reto