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

Re: No sort with except

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: No sort with except
Date: 2012-03-01 09:19:17
Message-ID: jineul$pdp$2@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

pgsql-sql by date

Next:From: Philip CoulingDate: 2012-03-01 09:30:21
Subject: Re: No sort with except
Previous:From: reto.buchliDate: 2012-03-01 08:56:04
Subject: Re: No sort with except

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