Received-SPF: pass (google.com: domain of couling@gmail.com designates
	10.216.135.66 as permitted sender) client-ip=10.216.135.66;
Message-ID: <4F4F41AD.4060408@pedal.me.uk>
Date: Thu, 01 Mar 2012 09:30:21 +0000
From: Philip Couling <phil@pedal.me.uk>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: reto.buchli@wsl.ch
CC: pgsql-sql@postgresql.org
Subject: Re: No sort with except
References: <OF83C68685.28BB0E71-ONC12579B4.002C0AFA-C12579B4.002D2D18@wsl.ch>
	<4F4F3075.3030506@frank.uvena.de>
	<OFC520BEC0.AF1827DF-ONC12579B4.0030869C-C12579B4.0031146A@wsl.ch>
In-Reply-To: 
 <OFC520BEC0.AF1827DF-ONC12579B4.0030869C-C12579B4.0031146A@wsl.ch>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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