Re: No sort with except - Mailing list pgsql-sql

<br /><tt><font size="2">pgsql-sql-owner@postgresql.org schrieb am 01.03.2012 09:16:53:<br /><br /> > From: Frank
Lanitz<frank@frank.uvena.de></font></tt><br /><tt><font size="2">> To: pgsql-sql@postgresql.org,
</font></tt><br/><tt><font size="2">> Date: 01.03.2012 09:16</font></tt><br /><tt><font size="2">> Subject: Re:
[SQL]No sort with except</font></tt><br /><tt><font size="2">> Sent by:
pgsql-sql-owner@postgresql.org</font></tt><br/><tt><font size="2">> <br /> > Am 01.03.2012 09:13, schrieb
reto.buchli@wsl.ch:<br/> > > Dear all,<br /> > > <br /> > > When I run the following SQL with
PostgreSQL9.1:<br /> > > <br /> > > -- <br /> > > SELECT DISTINCT ON (pernr) pernr, vorna, nachn,
eindt,ausdt, updat, status<br /> > >   FROM person<br /> > >        <br /> > > WHERE eindt <=
TO_CHAR(CURRENT_DATE,'YYYYMMDD')<br/> > >         AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')<br /> >
><br /> > > ORDER BY pernr, eindt DESC;<br /> > > -- <br /> > > <br /> > > it works. I get
themost recent persons, even if one came back within<br /> > > this time range.<br /> > > <br /> > >
Butif i do this:<br /> > > <br /> > > ---<br /> > > SELECT DISTINCT ON (pernr) pernr, vorna, nachn,
eindt,ausdt, updat, status<br /> > >   FROM person<br /> > >        <br /> > > WHERE eindt <=
TO_CHAR(CURRENT_DATE,'YYYYMMDD')<br/> > >         AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')<br /> >
>EXCEPT        <br /> > > <br /> > > SELECT DISTINCT ON (pernr) pernr,  vorna, nachn, eindt, ausdt,
updat,<br/> > > status<br /> > >   FROM person<br /> > >        RIGHT JOIN table ON sobid = CAST
(pernrAS VARCHAR) AND objid = 10<br /> > > WHERE eindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')<br /> > >    
   AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')<br /> > > ORDER BY pernr, eindt DESC;<br /> > > ---<br
/>> > <br /> > > In this case the ORDER BY does not work: I will get the same person<br /> > > data,
eitherwith DESC as with ASC, even when this should change.<br /> > > <br /> > > Does anyone have an
explanationfor this?<br /> > <br /> > <br /> > Don't you sort just the part at EXCEPT?<br /> > <br /> >
Cheers,<br/> > Frank<br /> > <br /> > <br /> Hi Frank</font></tt><br /><tt><font size="2">This may be. But as
Iunderstand, this will sort the result set. I'm also not able to place ORDER BY before the EXCEPT.</font></tt><br /><br
/><tt><fontsize="2">Am I wrong?</font></tt><br /><br /><tt><font size="2">Cheers,</font></tt><br /><tt><font
size="2">Reto<br/></font></tt>