Re: No sort with except

On 2012-03-01, reto(dot)buchli(at)wsl(dot)ch <reto(dot)buchli(at)wsl(dot)ch> wrote:
> Dies ist eine mehrteilige Nachricht im MIME-Format.
> --=_alternative 002D2CF5C12579B4_=
> Content-Type: text/plain; charset="US-ASCII"
>
> 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?

the ORDER BY is evaluated over the final result set, you can bind it to the
except part using parentheses.

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;
)

--
⚂⚃ 100% natural