Mysql: agrupar registros y obtener el mayor valor de cada uno

[liranan]

CONTEXTO:

Una página de juegos, la base de datos "partidas" recoge los datos de cada partida, qué juego se jugó, quiénes participaron (jugador1, jugador2, jugador3, jugador4), cómo quedó el ranking (puesto1, puesto2, puesto3, puesto4) y la puntuación que hizo cada uno (puntuacion1 corresponde al puesto1 y así hasta el 4), además de otros datos que no vienen al caso en esta consulta.

PROBLEMA:

Mi objetivo es mostrar a través de una tabla, la mayor puntuación jamás hecha en un juego. Es decir, la tabla debe mostrar:
- Cada juego una única vez
- La mayor puntuación que se ha hecho (comparando entre todos los resultados de los registros puntuacion1, puntuacion2, puntuacion3 y puntuacion4)
- Quién hizo esa puntuación (si la mayor puntuacion corresponde a la columna "puntuación1" se debe recoger el valor de la columna puesto1 y así sucesivamente).

SITUACIÓN ACTÚAL:
Puedo hacer cuatro consultas y comparar los resultados para obtener la mayor puntuación pero creo que es el método menos eficiente.
Actualmente he agrupado los resultados por juego, lo que hace que cada juego aparezca solo una vez, pero de ahí no paso. Tengo entendido que se puede combinar GROUP BY y ORDER BY pero no he conseguido hacerlo correctamente.

Estoy a tiempo de realizar cambios en la base de datos para optimizarla, así que cualquier consejo será bien recibido.
Gracias!
Gonzalo.

[quimfv]

Muestra la estructura de tus tablas y las relaciones entre ellas. Explicanos un poco las reglas del juego en referencia al numero de jugadores por partida (no hace falta que nos expliques el juego en si). Apartir de todo eso podemos orientarte para la query y mas importante sobre el diseño de las tablas....

De entrada 4 columnas no lo veo muy claro, siempre seran 4 jugadores o puede haber partidas con mas o menos jugadores.... Es mucho mas facil obtener el maximo de puntuacion si todos los resultados estan en el mismo campo.....y seguramente es mucho mas eficiente en cuanto a almacenamiento racional de los datos....

Pardidas
idPartida
fecha
...otros campos...

Jugadores
idJugador
Nombre
...

Resultados
idResultado
idPartida
idJugador
puntos


Con esta estructura es posible hacer todo lo que parece que quieres hacer...

Código MySQL:

SELECT j.nombre,r.puntos
FROM jugadores j INNER JOIN resultados r ON j.idjugador=r.idjugador
WHERE r.idpartida=X
ORDER BY r.puntos DESC;
 

El score de la partida X

Resultado maximo

Código MySQL:

SELECT p.idpartida,p.fecha,j.nombre,r.puntos
FROM (partidas p INNER JOIN resultados r ON p.idpartida=r.idpartida)
                            INNER JOIN jugadores j ON j.idjugador=r.idjugador
WHERE r.puntos = (SELECT MAX(r2.puntos) FROM resultados r2)
 

Las diez mejores puntuaciones
(partida, jugador y puntos)

Código MySQL:

SELECT p.idpartida,p.fecha,j.nombre,r.puntos
FROM (partidas p INNER JOIN resultados r ON p.idpartida=r.idpartida)
                            INNER JOIN jugadores j ON j.idjugador=r.idjugador
ORDER BY r.puntos DESC
LIMIT 10;
 

[liranan]

Muchísimas gracias por tu rápida respuesta, Quim. Viéndolo está claro que tienes un nivel más alto de mysql que yo, porque no acabo de comprender bien todo lo que me comentas.

Anoto todo lo que me has dicho para investigarlo, de todas formas dejo una mejor explicación de las tablas que tengo y cómo funcionan para facilitar la tarea a alguien que quiera darme algún consejillo.

MIS TABLAS

usuarios
-idUsuario
-nickname
-avatar
-datos personales, etc.

juegos
-idJuego
-imagen
-instrucciones, etc.

partidas
-idPartida
-tipoPartida (de 2 a 4 jugadores)
-jugador1
-jugador2
-jugador3 (puede quedar vacío)
-jugador4 (puede quedar vacío)

-puesto1 (ganador)
-puesto2
-puesto3 (puede quedar vacío)
-puesto4 (puede quedar vacío)

-puntuacion1 (del jugador1)
-puntuacion2 (del jugador2)
-puntuacion3 (del jugador3)
-puntuacion4 (del jugador4)

-fecha, etc.

Comprendo que me recomiendas crear una nueva tabla de resultados que se enlazaría con la de partidas, relajando el número de registros que ésta recoge, y que estaría a su vez enlazada con las otras. Me doy cuenta de que mi sistema quizás no es el mejor pero no sé si meterme en un cambio tan grande (mis jefes son un poco pesaos). Lo ves muy necesario?

[quimfv]

Tu estructura no supera el mas minimo analisis en el paradigma relacional

Tienes una estructura pensada para 4 jugadores cuando tu mismo preves que puede haber solo 2, mal uso del espacio en disco, en mi estructura el numero de jugadores es libre y no se desperdicia ni un bite.

Tienes campos calculados lo que supone un error de diseño de libro. Si una informacion se puede calcular a partir de otra ya guardada no se debe guardar ese dato calculado es inutil y peligroso (si tienes la puntuación no es necesario guardar el puesto).


Se complican mucho las querys y el mantenimiento de la bbdd.... sigo?

[liranan]

No, no, no hace falta que me hundas en la miseria, tomo nota.

Verás, como hombre orquesta en esta empresa hago diseño web, programación web, base de datos, diseño de juegos, ilustración, programación de juegos flash, y me falta fregar los suelos; como es comprensible no manejo todos los campos al mismo nivel, así que poco a poco voy aprendiendo como puedo para salir adelante.

Agradezco tu sugerencia, y de hecho, cualquier sugerencia es bienvenida, me doy cuenta que no tengo más remedio que modificar mi sistema de partidas, alguna sugerencia más antes de hacerlo?

[quimfv]

Tampoco era esa la intención....

En cuanto a mas sugerencias, no te puedo dar sin conocer a fondo las reglas de negoció que manejas.

Parece que tienes mas de un juego luego a mi estructura hay que agregar una tabla llamada juegos con su id y agregar ese id a la tabla partidas....

Asi en general te recomendaria algun buen manual de bbdd relacionales.... seguro que encuentras muchos en la red.... sobre todo lee la parte en que se define el modelo relacional y las reglas de normalización.

12 reglas de Codd

Formas Normales

[liranan]

Bueno, nueva semana y empezamos con buen pie.
Nada más llegar me he puesto a modificar de raiz mi sistema de tablas basándome en tus sugerencias, Quim y con la previa explicación y justificación a mi jefa.
El caso es que, en muy poco tiempo, he conseguido dividir las líneas de programación que necesitaba para hacer lo mismo en más o menos diez veces menos.
De forma que tengo que agradecerte tus consejos.

Ahora bien, aunque el sistema de creación de partidas es mucho más efectivo, soy incapaz de hacer las consultas de resultados a la hora de hacer las estadísticas, sin duda debido a que desconozco por completo el uso de la correcta combinación de tablas.

Así que si tú, o alguien me puede echar una mano, la situación actual es la siguiente:

TABLAS

usuarios
-nombre
-id
-datos personales...

partidas
-idPartida
-fecha
-tipo de partida (1 a 4 jugadores) (este campo sigue sin estar optimizado pero lo haré en el futuro)
- otros datos...

puntuaciones
-id
-idPartida (relacionado con la tabla partidas)
-idUsuario (relacionado con la tabla usuarios)
-puntuacion(recogida directamente del juego flash que sea)

SISTEMA
Al seleccionar un juego, se crea una partida, cuando todos los participantes se han unido, se cierra esa partida, cada uno juega por su cuenta y luego se comparan las puntuaciones dentro de la tabla puntuaciones con el mismo id de partida.

OBJETIVO A CUMPLIR AHORA
Después de terminar la partida, el usuario debe poder acceder a los siguientes datos:

Estadísticas
Una tabla con todas las partidas que ha jugado (o las 20 últimas por ejemplo), que incluye las siguientes informaciones:
- a qué juego se jugó (esto está solucionado)
- quiénes participaron en esa partida y qué puntuación hizo cada uno
- quién ganó
- fecha y otros datos...

Top
El objetivo inicial de esta consulta. una tabla que muestre:
- la mayor puntuación hecha jamás por algun usuario en cada uno de los juegos

Gracias por animarse a responder.
Gonzalo.

[quimfv]

Vistas las consultas que pides insisto en que te falta una tabla juegos, para poder diferenciar las partidas.

Estadísticas

Código MySQL:

SELECT *
FROM ((usuarios u INNER JOIN puntuaciones p ON u.idUsuario=p.idUsuario)
                             INNER JOIN partidas pr ON p.idPartida=pr.idPartida)
                             INNER JOIN juegos j ON j.idJuego=pr.idJuego
WHERE u.idUsuario=X
ORDER BY pr.fecha DESC
LIMIT 20;
 

Ultimas 20 partidas del usuario X, substiuye el * por la lista de campos que te interese.

Código MySQL:

SELECT p.idPartida
FROM puntuaciones p INNER JOIN partidas pr ON p.idPartida=pr.idPartida
WHERE p.idUsuario=X;
 

Id de las partidas de X

Con la primera query y la segunda

Código MySQL:

SELECT *
FROM ((usuarios u INNER JOIN puntuaciones p ON u.idUsuario=p.idUsuario)
                             INNER JOIN partidas pr ON p.idPartida=pr.idPartida)
                             INNER JOIN juegos j ON j.idJuego=pr.idJuego
WHERE pr.idPartida IN (SELECT p.idPartida
                                     FROM puntuaciones p2 
                                         INNER JOIN partidas pr2 
                                           ON p2.idPartida=pr2.idPartida
                               WHERE p2.idUsuario=X)
ORDER BY pr.fecha DESC,p.puntuacion DESC;
 

Todos los datos de las partidas de X, con los jugadores ordenados por puntuación.

Top

Código MySQL:

SELECT j.nombre,MAX(p.puntuacion) as Record
FROM (puntuaciones p INNER JOIN partidas pr ON p.idPartida=pr.idPartida)
                             INNER JOIN juegos j ON j.idJuego=pr.idJuego
GROUP BY j.nombre;
 

Ahí tienes los datos en bruto, solo te faltan los autores de esos records pero ya tienes ejemplos .... a partir de aqui trabaja las salidas....

[liranan]

Muchas gracias por seguir respondiendo, Quim, perdona por no haberte contestado antes pero entre unas cosas y otras no he podido.
Efectivamente he probado tu código y funciona a la perfección, me muestra los registros en orden de puntuación, ahora solo me queda pelearme para traducir eso a una tabla php correctamente.
Por cierto, ya añadí la tabla juegos que me decías.

Gracias por todo!
Gonzalo.