By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

how to group by a joined query?

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.