πρέπει να εμφανίζεται στη ρήτρα GROUP BY ή να χρησιμοποιείται σε μια αθροιστική συνάρτηση

Ναι, αυτό είναι ένα συνηθισμένο πρόβλημα συνάθροισης. Πριν από την SQL3 (1999), τα επιλεγμένα πεδία πρέπει να εμφανίζονται στη ρήτρα GROUP BY[*].

Για να παρακάμψετε αυτό το πρόβλημα, πρέπει να υπολογίσετε τη συνάθροιση σε ένα υποερώτημα και στη συνέχεια να την ενώσετε με τον εαυτό της για να λάβετε τις πρόσθετες στήλες που θα πρέπει να εμφανίσετε:

SELECT m.cname, m.wmname, t.mx
FROM (
    SELECT cname, MAX(avg) AS mx
    FROM makerar
    GROUP BY cname
    ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

Αλλά μπορείτε επίσης να χρησιμοποιήσετε τις συναρτήσεις παραθύρου, οι οποίες φαίνονται απλούστερες:

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;

Το μόνο πράγμα με αυτή τη μέθοδο είναι ότι θα εμφανίσει όλες τις εγγραφές (οι συναρτήσεις παραθύρου δεν ομαδοποιούν). Αλλά θα δείξει το σωστό (δηλ. μεγιστοποιημένο σε επίπεδο cname) MAX για τη χώρα σε κάθε γραμμή, οπότε εξαρτάται από εσάς:

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  |     5.0000000000000000
 spain  | usopp  |     5.0000000000000000

Η λύση, αναμφισβήτητα λιγότερο κομψή, για να δείξετε μόνο τις πλειάδες (cname, wmname) που ταιριάζουν με την τιμή max, είναι:

SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
    m.cname, m.wmname, t.avg AS mx
FROM (
    SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn 
    FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

[*]: Ενδιαφέρον είναι ότι, παρόλο που οι προδιαγραφές επιτρέπουν την επιλογή μη ομαδοποιημένων πεδίων, οι μεγάλες μηχανές δεν φαίνεται να το συμπαθούν ιδιαίτερα. Η Oracle και ο SQLServer απλά δεν το επιτρέπουν καθόλου. Η Mysql συνήθιζε να το επιτρέπει από προεπιλογή, αλλά τώρα από την έκδοση 5.7 ο διαχειριστής πρέπει να ενεργοποιήσει αυτή την επιλογή (ONLY_FULL_GROUP_BY) χειροκίνητα στη διαμόρφωση του διακομιστή για να υποστηρίζεται αυτή η λειτουργία...

Στο Postgres, μπορείτε επίσης να χρησιμοποιήσετε την ειδική σύνταξη DISTINCT ON (expression):

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;

SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
    SELECT cname, MAX(avg) max
    FROM makerar
    GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;

Χρήση της συνάρτησης rank() window function:

SELECT cname, wmname, avg
FROM (
    SELECT cname, wmname, avg, rank() 
    OVER (PARTITION BY cname ORDER BY avg DESC)
    FROM makerar) t
WHERE rank = 1;

Σημείωση

Οποιοδήποτε από τα δύο θα διατηρήσει πολλαπλές μέγιστες τιμές ανά ομάδα. Αν θέλετε μόνο μία εγγραφή ανά ομάδα ακόμα και αν υπάρχουν περισσότερες από μία εγγραφές με avg ίσο με max θα πρέπει να ελέγξετε την απάντηση του @ypercube's.