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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
