From: Frank Lanitz <frank@frank.uvena.de>
To: pgsql-sql@postgresql.org
Subject: Re: No sort with except
Date: Thu, 01 Mar 2012 09:16:53 +0100
Message-ID: <4F4F3075.3030506@frank.uvena.de> (raw)
In-Reply-To: <OF83C68685.28BB0E71-ONC12579B4.002C0AFA-C12579B4.002D2D18@wsl.ch>
References: <OF83C68685.28BB0E71-ONC12579B4.002C0AFA-C12579B4.002D2D18@wsl.ch>
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
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: frank@frank.uvena.de
Subject: Re: No sort with except
In-Reply-To: <4F4F3075.3030506@frank.uvena.de>
* 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