Date: Sun, 23 Jun 2002 14:22:17 +0200
From: Mathieu Arnold <mat@mat.cc>
To: Devrim GUNDUZ <devrim@oper.metu.edu.tr>, pgsql-sql@postgresql.org
Subject: Re: Select + min question 
Message-ID: <42040000.1024834936@denis-bsd.in.t-online.fr>
In-Reply-To: <Pine.LNX.4.44.0206231435370.11727-100000@oper.metu.edu.tr>
References: <Pine.LNX.4.44.0206231435370.11727-100000@oper.metu.edu.tr>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


--On Sunday, June 23, 2002 14:36:14 +0300 Devrim GUNDUZ
<devrim@oper.metu.edu.tr> wrote:

> 
> Hi,
> 
> I have created a table like this:
> 
> CREATE TABLE test (
> 	b_date char(8),
> 	active boolean,
> 	id serial
> );
> 
> and inserted some rows.
> 
> 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
> 
> What should I do? ANy suggestions?

I believe that :
SELECT   b_date, id   FROM   test   WHERE active='t' ORDER BY id LIMIT 1;
is the right thing.

-- 
Mathieu Arnold