Re: [GENERAL] how to group by a joined query?

[Weiping He]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html