[SQL] Select + min question

[Devrim GUNDUZ]

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

[EMAIL PROTECTED]
[EMAIL PROTECTED]

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





---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Select + min question

[Christopher Kings-Lynne]

> 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



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Select + min question

[Mathieu Arnold]

--On Sunday, June 23, 2002 14:36:14 +0300 Devrim GUNDUZ
<[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org