pgsql-sql-ow...@postgresql.org schrieb am 01.03.2012 09:16:53: > From: Frank Lanitz <fr...@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-ow...@postgresql.org > > Am 01.03.2012 09:13, schrieb reto.buc...@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
