Skip site navigation (1) Skip section navigation (2)

Re: No sort with except

From: Frank Lanitz <frank(at)frank(dot)uvena(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: No sort with except
Date: 2012-03-01 08:16:53
Message-ID: 4F4F3075.3030506@frank.uvena.de (view raw or flat)
Thread:
Lists: pgsql-sql
Am 01.03.2012 09:13, schrieb reto(dot)buchli(at)wsl(dot)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


In response to

Responses

pgsql-sql by date

Next:From: reto.buchliDate: 2012-03-01 08:56:04
Subject: Re: No sort with except
Previous:From: reto.buchliDate: 2012-03-01 08:13:26
Subject: No sort with except

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group