how to group by a joined query?

[Weiping He]

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

Laser
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[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

[Tom Lane]

Weiping He <la***@zhengmai .com.cn> writes:

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: is it specification compliant or postgresql specific?

It's spec-compliant according to SQL92.

There is some amazingly complex verbiage in SQL99 that seems intended to
allow the system to deduce that some GROUP BY specs are redundant and
can be omitted. We don't implement that (yet) and I don't think it
would apply to your example anyway, since neither table's name column
is marked as primary key.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match