No sort with except - Mailing list pgsql-sql
| From | reto.buchli@wsl.ch |
|---|---|
| Subject | No sort with except |
| Date | |
| Msg-id | OF83C68685.28BB0E71-ONC12579B4.002C0AFA-C12579B4.002D2D18@wsl.ch Whole thread Raw |
| Responses |
Re: No sort with except
(Frank Lanitz <frank@frank.uvena.de>)
|
| List | pgsql-sql |
<font face="sans-serif" size="2">Dear all,</font><br /><br /><font face="sans-serif" size="2">When I run the following SQLwith PostgreSQL 9.1:</font><br /><br /><font face="sans-serif" size="2">--</font><br /><font face="sans-serif" size="2">SELECTDISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status</font><br /><font face="sans-serif" size="2"> FROM person</font><br /><font face="sans-serif" size="2"> </font><br /><font face="sans-serif" size="2">WHEREeindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')</font><br /><font face="sans-serif" size="2"> AND ausdt>= TO_CHAR(CURRENT_DATE,'YYYYMMDD') </font><br /><br /><font face="sans-serif" size="2">ORDER BY pernr, eindt DESC;</font><br/><font face="sans-serif" size="2">--</font><br /><br /><font face="sans-serif" size="2">it works. I get themost recent persons, even if one came back within this time range.</font><br /><br /><font face="sans-serif" size="2">Butif i do this:</font><br /><br /><font face="sans-serif" size="2">---</font><br /><font face="sans-serif" size="2">SELECTDISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status</font><br /><font face="sans-serif" size="2"> FROM person</font><br /><font face="sans-serif" size="2"> </font><br /><font face="sans-serif" size="2">WHEREeindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')</font><br /><font face="sans-serif" size="2"> AND ausdt>= TO_CHAR(CURRENT_DATE,'YYYYMMDD') </font><br /><font face="sans-serif" size="2">EXCEPT </font><br /><br/><font face="sans-serif" size="2">SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status</font><br/><font face="sans-serif" size="2"> FROM person </font><br /><font face="sans-serif" size="2"> RIGHTJOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid = 10</font><br /><font face="sans-serif" size="2">WHERE eindt<= TO_CHAR(CURRENT_DATE,'YYYYMMDD')</font><br /><font face="sans-serif" size="2"> AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')</font><br /><font face="sans-serif" size="2">ORDER BY pernr, eindt DESC;</font><br /><fontface="sans-serif" size="2">---</font><br /><br /><font face="sans-serif" size="2">In this case the ORDER BY does notwork: I will get the same person data, either with DESC as with ASC, even when this should change.</font><br /><br /><fontface="sans-serif" size="2">Does anyone have an explanation for this?</font><br /><br /><font face="sans-serif" size="2">ManyThanks</font><br /><br />