From: | Weiping He <laser(at)zhengmai(dot)com(dot)cn> |
---|---|
To: | Weiping He <laser(at)zhengmai(dot)com(dot)cn> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to group by a joined query? |
Date: | 2003-09-01 03:38:58 |
Message-ID: | 3F52BF52.5020106@zhengmai.com.cn |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Weiping He wrote:
> suppose I've got two table:
>
> laser_uni=# \d t1
> Table "public.t1"
> Column | Type | Modifiers
> --------+------+-----------
> name | text |
> addr | text |
>
> laser_uni=# \d t2
> Table "public.t2"
> Column | Type | Modifiers
> --------+---------+-----------
> name | text |
> len | integer |
> of | integer |
>
> and I want to use join to select out data and then group by one
> column, like this:
>
> laser_uni=# select t1.name, t1.addr, t2.name, t2.len, t2.of from t1
> right join t2 on t1.name=t2.name group by t2.name;
> ERROR: Attribute t1.name must be GROUPed or used in an aggregate
> function
>
> seems the I must gorup all those fields:
>
> laser_uni=# select t1.name as t1name, t1.addr as t1addr, t2.name as
> t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group
> by t1.name, t1.addr, t2.name, t2.len, t2.of;
> t1name | t1addr | t2name | len | of
> --------+--------+--------+-----+----
> | | henry | 2 | 4
> | | laser | 4 | 4
> (2 rows)
>
> is it specification compliant or postgresql specific?
>
> Thanks
>
reread the docs, seems use DISTINCE ON clause solved my problem:
select distinct on( t2.len) t1.name as t1name, t1.addr as t1addr,
t2.name as t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name;
Thanks
Laser
From | Date | Subject | |
---|---|---|---|
Next Message | mgarriss | 2003-09-01 03:40:47 | Getting last inserted SERIAL |
Previous Message | Hal Vorlee | 2003-09-01 03:34:12 | XA Resource Manager |