[aide requête SQL] séléctionner les meilleurs scores - mais un seul score par utilisateur enregistré

[grosbouff]

Bonjour, j'ai fait un jeu pour un site.
Je sauve différentes infos dans deux tables :
-table utilisateurs ("users")

Code :

1
2
3
id 	games 	firstname 	lastname 	password 	email 		 	time
-----------------------------------------------------------------------------------------------------
17709 	0 	titi 		test 	  	*** 		perso@**.be 	2011-05-09 15:17:13

-table scores ("hall_of_fame")

Code :

1
2
3
4
id	user_id		score		charname		time
---------------------------------------------------------------------------
29	17709	 	475		cheezy		2011-05-22 16:13:30
74	17709	 	520		salsa		2011-05-22 21:15:24

Ce que je voudrais faire,
C'est une requête qui sélectionne les meilleurs scores pour un mois spécifique, en les triant par ordre de scores décroissant.

Attention toutefois, les utilisateurs peuvent jouer autant de fois qu'ils le souhaitent, et j'aimerais que les résultats de la requête ne me sortent que le meilleur résultat des utilisateurs.

Donc en gros
-filtrer les meilleurs résultats
-selon un mois
-en évitant les doublons (conserver seulement le meilleur score de l'utilisateur)


J'ai déjà travaillé à construire la requête, mais je ne suis pas sûr qu'elle soit correcte. En tout cas, elle fonctionne, mais me renvoie des doublons pour les utilisateurs.

Merci pour votre aide !

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
 
users.id,
users.games,
users.firstname,
users.lastname,
users.email,
users.address,
users.city,
users.zipcode,
users.phone,
users.spam,
 
hall_of_fame.score,
hall_of_fame.time
 
FROM hall_of_fame
INNER JOIN users ON hall_of_fame.user_id=users.id
 
WHERE YEAR(hall_of_fame.time) = '2011'
AND MONTH(hall_of_fame.time) = '5'
 
ORDER BY hall_of_fame.score DESC

[Maljuna Kris]

Saluton,
Il te suffit, dans ta requête de changer hall_of_fame.score, par MAX(hall_of_fame.score) AS score
et d'ajouter dans ta requête, avant la ligne ORDER BY où tu changeras hall_of_fame.score par score, GROUP BY users.id
Par contre la valeur de hall_of_fame.time retenue, si elle correspondra bien aux critères de la clause WHERE YEAR(hall_of_fame.time) = '2011'
AND MONTH(hall_of_fame.time) = '5', ne sera pas forcément celle de la ligne contenant le score MAX.

[grosbouff]

Envoyé par Maljuna Kris

Saluton,
Il te suffit, dans ta requête de changer hall_of_fame.score, par MAX(hall_of_fame.score) AS score
et d'ajouter dans ta requête, avant la ligne ORDER BY où tu changeras hall_of_fame.score par score, GROUP BY users.id
Par contre la valeur de hall_of_fame.time retenue, si elle correspondra bien aux critères de la clause WHERE YEAR(hall_of_fame.time) = '2011'
AND MONTH(hall_of_fame.time) = '5', ne sera pas forcément celle de la ligne contenant le score MAX.

Hello et merci pour ta réponse (rapide) !
Donc ce que tu veux me dire, c'est que cela retournera bien les résultats que je veux (et sur la bonne période, càd à l'intérieur d'un mois -ici, mai-), mais que le champs hall_of_fame.time retourné ne sera pas spécialement celui de la ligne correspondante au meilleur score, c'est ça ?

Si oui, c'est pas un problème.
Mais je remarque que le "hall_of_fame.id" retourné ne correspond pas non plus au score affiché.
Possible de faire correspondre ça (et le hall_of_fame.time, tant qu'on y est, c'est tjs mieux) ? Ce serait plus facile...

Voici ce que j'ai pour l'instant donc

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
 
users.id AS user_id,
users.firstname,
users.lastname,
users.email,
users.address,
users.city,
users.zipcode,
users.phone,
users.spam,
hall_of_fame.id AS game_id,
MAX(hall_of_fame.score) AS score,
users.games AS total_games
 
FROM hall_of_fame
 
INNER JOIN users ON hall_of_fame.user_id=users.id
 
WHERE YEAR(hall_of_fame.time) = '2011'
AND MONTH(hall_of_fame.time) = '5'
 
GROUP BY users.id
 
ORDER BY score DESC

...Is that it ?
merci !

[ced]

Bonjour,

C'est une question qui est souvent posée.
Par exemple, tu peux t'inspirer de la solution donnée dans la discussion suivante : http://www.developpez.net/forums/d10...s/aide-select/
Ou encore, une autre solution proposée dans les sources MySQL : http://mysql.developpez.com/telechar...aque-categorie

[skuatamad]

Normal que ça ne fonctionne pas, toutes les colonnes présentes dans le SELECT et non aggrégées doivent être dans le GROUP BY.
Seul MySql permet de déroger à cette règle et décide de renvoyer un résultat aléatoire donc faux. A lire sur le GROUP BY et MySql

Avec MySql, pas le choix, il faut faire des sous-requêtes :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT u.id AS user_id, u.firstname, u.lastname, u.email, u.address,
       u.city, u.zipcode, u.phone, u.spam, u.games AS total_games,
       h2.id AS game_id, h2.max_score AS score, h2.time
  FROM users u
  JOIN (SELECT h.id, s.user_id, s.max_score, h.time
          FROM hall_of_fame h
          JOIN (SELECT user_id, max(score) AS max_score
                  FROM hall_of_fame
                 WHERE YEAR(hall_of_fame.time) = 2011
                   AND MONTH(hall_of_fame.time) = 5
                 GROUP BY user_id
                ) s
             ON h.user_id = s.user_id AND h.score = s.max_score
       ) h2 ON u.user_id = h2.user_id
 ORDER BY h2.max_score DESC

Cette requête renverra toujours des doublons si 1 user fait plusieurs fois le même score dans le mois.
Il faudra donc créer un autre niveau de sous-requête si tu choisis de ne prendre que le meilleur score le plus récent par exemple.

[grosbouff]

Merci skuatamad,
Malheureusement cela ne fonctionne pas: Unknown column 'u.user_id' in 'on clause'

J'ai changé pour

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT u.id AS user_id, u.firstname, u.lastname, u.email, u.address, u.city, u.zipcode, u.phone, u.spam, u.games AS total_games, h2.id AS game_id, h2.max_score AS score, h2.time
FROM users u
JOIN (
 
SELECT h.id, s.user_id, s.max_score, h.time
FROM hall_of_fame h
JOIN (
 
SELECT user_id, max( score ) AS max_score
FROM hall_of_fame
WHERE YEAR( hall_of_fame.time ) =2011
AND MONTH( hall_of_fame.time ) =5
GROUP BY user_id
)s ON h.user_id = s.user_id
AND h.score = s.max_score
)h2 ON u.id = h2.user_id
ORDER BY h2.max_score DESC

Mon changement concerne

Code :

h2 ON u.user_id = h2.user_id

qui a été modifié pour

Code :

h2 ON u.id = h2.user_id

En parlant de ça, pourquoi est-ce que j'ai des résultats différents si je fais

Code :

h2 ON user_id = h2.user_id

user_id (sans préfixe du genre "u.") ayant été défini ici

Code :

SELECT u.id AS user_id

Bref, si la requête est déjà correcte comme ça; même avec des doublons concernant les scores, c'est déjà pas mal.

[skuatamad]

Je me suis planté dans le nom de la colonne, dans ma requête ça devrait plutôt être :
h2 ON u.id = h2.user_id