From: Philip Couling <phil@pedal.me.uk>
To: reto.buchli@wsl.ch
Cc: pgsql-sql@postgresql.org
Subject: Re: No sort with except
Date: Thu, 01 Mar 2012 09:30:21 +0000
Message-ID: <4F4F41AD.4060408@pedal.me.uk> (raw)
In-Reply-To: <OFC520BEC0.AF1827DF-ONC12579B4.0030869C-C12579B4.0031146A@wsl.ch>
References: <OF83C68685.28BB0E71-ONC12579B4.002C0AFA-C12579B4.002D2D18@wsl.ch>
<4F4F3075.3030506@frank.uvena.de>
<OFC520BEC0.AF1827DF-ONC12579B4.0030869C-C12579B4.0031146A@wsl.ch>
Hi Reto
You are right to assume that you're query is ordering the second select
and not the whole query. To order the query as a whole it in
parentheses and put the ORDER BY at the end:
(
SELECT foo FROM X
EXCEPT
SELECT foo FROM Y
) ORDER BY foo;
Hope this helps
On 01/03/2012 08:56, reto.buchli@wsl.ch wrote:
>
> pgsql-sql-owner@postgresql.org schrieb am 01.03.2012 09:16:53:
>
>> From: Frank Lanitz <frank@frank.uvena.de>
>> To: pgsql-sql@postgresql.org,
>> Date: 01.03.2012 09:16
>> Subject: Re: [SQL] No sort with except
>> Sent by: pgsql-sql-owner@postgresql.org
>>
>> 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
>>
>>
> Hi Frank
> This may be. But as I understand, this will sort the result set. I'm
> also not able to place ORDER BY before the EXCEPT.
>
> Am I wrong?
>
> Cheers,
> Reto
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: phil@pedal.me.uk, reto.buchli@wsl.ch
Subject: Re: No sort with except
In-Reply-To: <4F4F41AD.4060408@pedal.me.uk>
* 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