我有一张桌子看起来像是打电话的"makerar"
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
我想 for each cname Select 最大平均值.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
但我会出错,
ERROR: column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
所以我这么做
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
然而,这不会给出预期的结果,下面显示的输出不正确.
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
实际结果应该是
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
我如何着手解决这个问题?
注意:此表是根据以前的操作创建的视图.