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, [email protected] wrote: > > [email protected] schrieb am 01.03.2012 09:16:53: > >> From: Frank Lanitz <[email protected]> >> To: [email protected], >> Date: 01.03.2012 09:16 >> Subject: Re: [SQL] No sort with except >> Sent by: [email protected] >> >> Am 01.03.2012 09:13, schrieb [email protected]: >> > 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
