pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect + min question
4+ messages / 3 participants
[nested] [flat]
* Select + min question
@ 2002-06-23 11:36 Devrim GUNDUZ <devrim@oper.metu.edu.tr>
0 siblings, 2 replies; 4+ messages in thread
From: Devrim GUNDUZ @ 2002-06-23 11:36 UTC (permalink / raw)
To: pgsql-sql
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
-------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select + min question
@ 2002-06-23 12:16 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Devrim GUNDUZ <devrim@oper.metu.edu.tr>
1 sibling, 1 reply; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-06-23 12:16 UTC (permalink / raw)
To: Devrim GUNDUZ <devrim@oper.metu.edu.tr>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select + min question
@ 2002-06-23 12:22 Mathieu Arnold <mat@mat.cc>
parent: Devrim GUNDUZ <devrim@oper.metu.edu.tr>
1 sibling, 0 replies; 4+ messages in thread
From: Mathieu Arnold @ 2002-06-23 12:22 UTC (permalink / raw)
To: Devrim GUNDUZ <devrim@oper.metu.edu.tr>; pgsql-sql
--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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select + min question
@ 2002-06-24 06:18 Devrim GUNDUZ <devrim@oper.metu.edu.tr>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Devrim GUNDUZ @ 2002-06-24 06:18 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-sql
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
-------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-06-24 06:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-23 11:36 Select + min question Devrim GUNDUZ <devrim@oper.metu.edu.tr>
2002-06-23 12:16 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-06-24 06:18 ` Devrim GUNDUZ <devrim@oper.metu.edu.tr>
2002-06-23 12:22 ` Mathieu Arnold <mat@mat.cc>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox