Received: from mailout02.sul.t-online.de (mailout02.sul.t-online.com [194.25.134.17]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7T6S8f57540 for ; Wed, 29 Aug 2001 02:28:09 -0400 (EDT) (envelope-from A_Schnabel@t-online.de) Received: from fwd05.sul.t-online.de by mailout02.sul.t-online.de with smtp id 15bypa-0005LX-02; Wed, 29 Aug 2001 08:28:06 +0200 Received: from taska (520019441306-0001@[62.226.22.86]) by fwd05.sul.t-online.com with smtp id 15bypU-0WTh9EC; Wed, 29 Aug 2001 08:28:00 +0200 Message-ID: <003001c13053$a3c4ff60$0201a8c0@aschnabel.homeip.net> From: A_Schnabel@t-online.de (Andre Schnabel) To: "Joseph Shraibman" , "ML: Postgres SQL" References: <3B8AD867.3080007@selectacast.net> <13357.998962545@sss.pgh.pa.us> <3B8BFFAF.6050909@selectacast.net> Subject: Re: WHERE on an alias Date: Wed, 29 Aug 2001 08:27:12 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Sender: 520019441306-0001@t-dialin.net X-Archive-Number: 200108/346 X-Sequence-Number: 4596 ----- Original Message ----- From: "Joseph Shraibman" Subject: Re: [SQL] WHERE on an alias > If I try to put a distinct on in my subselect int the from I get: > > ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions > > what does that mean? You need to (at least) ORDER BY the expression you want to have the DISTINCT ON. Tom's example would work fine: > > select distinct on (ml.f1,ml.f2) * from ut,ml > > where ut.f1 = ml.f1 and ut.f2 = ml.f2 > > order by ml.f1, ml.f2, ml.f3 desc; Wrong: select distinct on (ml.f1,ml.f2) * from ut,ml where ut.f1 = ml.f1 and ut.f2 = ml.f2 order by ml.f2, ml.f1, ml.f3 desc; Andre