Choisir une ligne particulière par groupe ?

Question

J'ai une table appelée ratings qui ressemble à ceci

Rating   |   Picid    |   User
   8           12          6
   7           12          6
   9           15          7
   5           16          7
   9           17          8
   10          2           8
   7           18          3
   10          22          12

Je veux regrouper les évaluations en fonction du picid, mais ne choisir que le picid le mieux noté par utilisateur. J'ai déjà la requête comme ceci :

SELECT *, AVG(rating) AS total FROM ratings
  GROUP BY picid ORDER by total DESC

À l'heure actuelle, la requête affiche les informations de la manière suivante :

Rating   |   Picid    |   User
 7.5           12          6
   9           15          7
   5           16          7
   9           17          8
   10          2           8
   7           18          3
   10          22          12

Il a donc réussi à regrouper les évaluations en fonction du picid, mais je ne veux que l'évaluation la plus élevée par utilisateur. Je veux qu'il produise une information comme celle-ci :

Rating   |   Picid    |   User
 7.5           12          6
   9           15          7
   10          2           8
   7           18          3
   10          22          12

Vous voyez qu'il n'autorise que le picid le mieux noté par utilisateur ? Comment puis-je modifier ma requête pour faire cela ?

Faites-moi savoir si vous avez besoin de plus de précisions sur ce que je demande.

Answer 1

Vous devez d'abord calculer les notes moyennes pour chaque (Picid,User) puis trouver la maximum par groupe sur ce résultat matérialisé :

SELECT Rating, PicID, User
FROM (
  SELECT   AVG(Rating) AS Rating, Picid, User
  FROM     ratings
  GROUP BY Picid, User
) t1 NATURAL JOIN (
  SELECT   MAX(Rating) AS Rating, User
  FROM     (
    SELECT   AVG(Rating) AS Rating, User
    FROM     ratings
    GROUP BY Picid, User
  ) t
  GROUP BY User
) t2

Voir sur sqlfiddle .

Answer 2

Je ne pense pas que ce soit la meilleure méthode, mais elle fonctionne :

SELECT MAX(total) as maxTotal, picid, User 
FROM 
   (SELECT *, AVG(rating) AS total 
    FROM ratings 
    GROUP BY picid 
    ORDER BY total DESC) as a 
GROUP BY User 
ORDER BY maxTotal desc;

Vous pouvez utiliser ce sqlfiddle pour en essayer d'autres.