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(dot)buchli(at)wsl(dot)ch wrote:
>
> pgsql-sql-owner(at)postgresql(dot)org schrieb am 01.03.2012 09:16:53:
>
>> From: Frank Lanitz <frank(at)frank(dot)uvena(dot)de>
>> To: pgsql-sql(at)postgresql(dot)org,
>> Date: 01.03.2012 09:16
>> Subject: Re: [SQL] No sort with except
>> Sent by: pgsql-sql-owner(at)postgresql(dot)org
>>
>> Am 01.03.2012 09:13, schrieb reto(dot)buchli(at)wsl(dot)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