Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 8F8471014388 for ; Thu, 1 Mar 2012 05:31:49 -0400 (AST) Received: from plane.gmane.org ([80.91.229.3]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S32MV-0007jf-80 for pgsql-sql@postgresql.org; Thu, 01 Mar 2012 09:31:49 +0000 Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1S32MG-0003ji-79 for pgsql-sql@postgresql.org; Thu, 01 Mar 2012 10:31:32 +0100 Received: from ip-118-90-83-217.xdsl.xnet.co.nz ([118.90.83.217]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 01 Mar 2012 10:31:32 +0100 Received: from jasen by ip-118-90-83-217.xdsl.xnet.co.nz with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 01 Mar 2012 10:31:32 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: Jasen Betts Subject: Re: No sort with except Date: 1 Mar 2012 09:19:17 GMT Organization: Dis (not Dat) Organisation Lines: 74 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: ip-118-90-83-217.xdsl.xnet.co.nz X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W; (T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q; 8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^ User-Agent: slrn/pre1.0.0-18 (Linux) X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/5 X-Sequence-Number: 36400 On 2012-03-01, reto.buchli@wsl.ch wrote: > Dies ist eine mehrteilige Nachricht im MIME-Format. > --=_alternative 002D2CF5C12579B4_= > Content-Type: text/plain; charset="US-ASCII" > > 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? the ORDER BY is evaluated over the final result set, you can bind it to the except part using parentheses. 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; ) -- ⚂⚃ 100% natural