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