Обсуждение: Select + min question

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?

Best regards.
-- 

Devrim GUNDUZ

devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr

Web : http://devrim.oper.metu.edu.tr
-------------------------------------

> 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

--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

Hi,

On Sun, 23 Jun 2002, Christopher Kings-Lynne wrote:
> 
> 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;

It'a a shame for me that I could not think of using subselects :-(

Anyway, thanks.

Best regards.

-- 

Devrim GUNDUZ

devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr

Web : http://devrim.oper.metu.edu.tr
-------------------------------------