Re: No sort with except

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