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