Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 177031014388 for ; Thu, 1 Mar 2012 04:16:43 -0400 (AST) Received: from [2001:4d88:1ffc:463::1] (helo=uvena.de) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S31Bp-0003bM-MG for pgsql-sql@postgresql.org; Thu, 01 Mar 2012 08:16:42 +0000 Received: from [141.35.39.175] (c319808.zuv.uni-jena.de [141.35.39.175]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by uvena.de (Postfix) with ESMTPSA id CDD92254016 for ; Thu, 1 Mar 2012 08:16:24 +0000 (UTC) Message-ID: <4F4F3075.3030506@frank.uvena.de> Date: Thu, 01 Mar 2012 09:16:53 +0100 From: Frank Lanitz User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: No sort with except References: In-Reply-To: X-Enigmail-Version: 1.3.5 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Host-Lookup-Failed: Reverse DNS lookup failed for 2001:4d88:1ffc:463::1 (failed) X-Pg-Spam-Score: 0.8 (/) X-Archive-Number: 201203/2 X-Sequence-Number: 36397 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