I have a table that looks like this caller ‘makerar’
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
And I want to select the maximum avg for each cname.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
but I will get an error,
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;
so i do this
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
however this will not give the intented results, and the incorrect output below is shown.
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Actual Results should be
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
How can I go about fixing this issue?
Note: This table is a VIEW created from a previous operation.