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
