Re: how to group by a joined query?

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