Обсуждение: how to group by a joined query?

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

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

Weiping He <laser@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