Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id A11E4475959 for ; Sun, 23 Jun 2002 08:19:54 -0400 (EDT) Received: from mail.iinet.net.au (symphony-01.iinet.net.au [203.59.3.33]) by postgresql.org (Postfix) with SMTP id 9A015475939 for ; Sun, 23 Jun 2002 08:19:51 -0400 (EDT) Received: (qmail 20403 invoked by uid 666); 23 Jun 2002 12:19:53 -0000 Received: from unknown (HELO SOL) (203.59.159.113) by mail.iinet.net.au with SMTP; 23 Jun 2002 12:19:53 -0000 Message-ID: <002001c21aaf$c73cba40$0200a8c0@SOL> From: "Christopher Kings-Lynne" To: "Devrim GUNDUZ" , References: Subject: Re: Select + min question Date: Sun, 23 Jun 2002 20:16:16 +0800 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.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 X-Archive-Number: 200206/314 X-Sequence-Number: 8279 > Now, what I want is to select the b_date which has the minimum "id" and > active=t. > > A normal query would be : > SELECT b_date,min(id) FROM test WHERE active='t' GROUP BY id LIMIT 1 > > However, I only want to select b_date. So, if I perform the following > query, I get the following error: > > devrim=# SELECT b_date FROM test WHERE active='t' AND id=min(id) LIMIT 1; > ERROR: Aggregates not allowed in WHERE clause Use a subselect (and don't compare to 't' if it's a boolean field...) SELECT b_date FROM test WHERE active AND id=(SELECT min(id) FROM test) LIMIT 1; Chris