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
