From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: No sort with except
Date: 1 Mar 2012 09:19:17 GMT
Message-ID: <jineul$pdp$2@reversiblemaps.ath.cx> (raw)
References: <OF83C68685.28BB0E71-ONC12579B4.002C0AFA-C12579B4.002D2D18@wsl.ch>
On 2012-03-01, reto.buchli@wsl.ch <reto.buchli@wsl.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: No sort with except
In-Reply-To: <jineul$pdp$2@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox