Обсуждение: No sort with except
<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 />
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
<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>
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
On 2012-03-01, reto.buchli@wsl.ch <reto.buchli@wsl.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 = 10WHERE eindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD') AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')ORDER BY pernr, eindt DESC;) -- ⚂⚃ 100% natural