Resultado muy raro en consulta

[rafaelincl68]

Hola compañeros, ando liado con una consulta y tengo un problemilla, y es que tengo una BD con ciertos datos en ella, para los campos ID_ENSAYO, NIVEL, ACIERTOS.

ID_ENSAYO es clave primaria autoincrementada.

Necesito los ID's de los mejores ensayos en cada nivel, con lo cual para hacerlo hago la siguiente consulta :

select id_ensayo, nivel, max(aciertos)
from ensayos
group by nivel;

El caso es que me muestra los max(aciertos) bien, pero el ID_ENSAYO lo que hace es mostrarme el primero de la tabla que se encuentra, con lo cual no se corresponde esos aciertos maximos con su ID verdadero.

Sabeis que puede estar ocurriendo ??

Saludos y gracias a todos.

Rafalin.

[jurena]

Sí, con el group by sólo puedes traerte en la primera consulta el valor del MAX() y el campo por el que agrupas. Si quieres recupararlo todo, tienes que hacerlo así:


select e.id_ensayo, t1.niv, t1.maximo from ensayos e INNER JOIN (select nivel niv, max(aciertos) maximo from ensayos group by nivel)t1 ON e.nivel = t1.niv AND e.aciertos = t1.maximo ORDER BY e.id_ensayo, t1.maximo

Si dos id_ensayo pertenecientes al mismo nivel pudieran tener el mismo número de aciertos y fuera el máximo, te sacaría los dos (o los que fueran). No lo he probado.

[rafaelincl68]

Iniciado por jurena

Sí, con el group by sólo puedes traerte en la primera consulta el valor del MAX() y el campo por el que agrupas. Si quieres recupararlo todo, tienes que hacerlo así:


select e.id_ensayo, t1.niv, t1.maximo from ensayos e INNER JOIN (select nivel niv, max(aciertos) maximo from ensayos group by nivel)t1 ON e.nivel = t1.niv AND e.aciertos = t1.maximo ORDER BY e.id_ensayo, t1.maximo

Si dos id_ensayo pertenecientes al mismo nivel pudieran tener el mismo número de aciertos y fuera el máximo, te sacaría los dos (o los que fueran). No lo he probado.

Gracias compañero, lo acabo de probar y ahora si sale el resultado correcto. La unica pega que tengo es que como bien dices, cuando dos niveles con el mismo id_ensayo tienen el mismo numero de aciertos, pues muestra los dos resultados.

Para deshacer este empate, tengo otro campo en la tabla, llamado TIEMPO, el cual a igualdad de aciertos en un mismo nivel con el mismo id_ensayo, entonces el minimo tiempo es el que se deberia mostrar.

He estado intentando ampliar la consulta para obtener resultados unicos, aplicando el tiempo como te comento, pero tampoco he conseguido hacerlo, pues imagino que sera anidar otro INNER JOIN, con otro group by para pillar el minimo tiempo de los maximos.

Si puedes orientarme un poco sobre esto, pues lo agradeceria, pues el tema de los INNER JOIN no lo domino demasiado.

Saludos y gracias.

Rafalin.

[jurena]

Creo que no es necesario anidar nada más.

select e.id_ensayo, t1.niv, t1.maximo from ensayos e INNER JOIN (select nivel niv, max(aciertos) maximo from ensayos group by nivel)t1 ON e.nivel = t1.niv AND e.aciertos = t1.maximo ORDER BY e.tiempo LIMIT 1

Imagino que tiempo será también un campo de la tabla ensayos. Ordeno por tiempo más corto y luego limito a uno. Me mostrará sólo el primero.

[rafaelincl68]

Al hacerlo de este modo, lo que ocurre que la consulta solo me muestra 1 resultado.

En mi caso, como bien dices, TIEMPO, es un campo de la tabla ensayos. El resultado que debiera obtener, seria 8 tuplas, que serian los 8 niveles que tengo, cada uno de ellos con su maximo en aciertos, y en caso de empate, minimo en tiempo.

Haciendo la primera consulta que me indicaste me salen 9 tuplas, pues hay dos empates en un nivel, por eso te comentaba de anidar, para sacar el minimo de esos dos empates ...

Por mas vueltas que le doy ... la consulta tiene un aspecto horroroso pero no fufa ...

Como crees que podria hacerlo para obtener esas 8 tuplas ??

Saludos y gracias.

Rafalin.

[jurena]

Llevas razón, perdona. No me di cuenta de que estamos agrupando.

SELECT e2.id_ensayo, e2.tiempo, e2.nivel, e2.aciertos from ensayos e2 INNER JOIN (select e.id_ensayo, min(e.tiempo) minimo, e.nivel, t1.maximo from ensayos e INNER JOIN (select nivel niv, max(aciertos) maximo from ensayos group by nivel)t1 ON e.nivel = t1.niv AND e.aciertos = t1.maximo group by e.nivel)t2 ON e2.tiempo = t2.minimo and e2.nivel = t1.niv AND e2.aciertos = t1.maximo

Prueba esto. No he probado nada y seguro que se me ha escapado algo. Como temías, requiere una anidación más, pero si la realizas de un modo correcto tendrás lo que quieres.

he editado para corregir errores, pero seguro que todavía queda alguno.

[rafaelincl68]

Perdona dices ?? Joder encima con la lata que te estoy dando, creo que el deberia pedirte perdon soy yo, jejeje.

Bueno, y yendo al tema, el caso es que efectivamente algo falla Lo he estado revisando, e intentando buscar el fallo, pero da un error de "columna desconocida", tanto en t1.minimo, como en e.nivel, como t1.maximo ....

Pongo aqui el codigo que has puesto, un poco retocado para que se pueda entender mejor. Como supuse debia ser anidada, pero macho, los INNER JOIN nunca los he usado, y si encima son anidados entonces

SELECT e2.id_ensayo, e2.tiempo, e2.nivel, e2.aciertos
FROM ensayos e2
INNER JOIN (

SELECT e.id_ensayo, min(e.tiempo) minimo, e.nivel, t1.maximo
FROM ensayos e
INNER JOIN (SELECT nivel niv, max(aciertos) maximo FROM ensayos

GROUP BY nivel) t1 ON e.nivel = t1.niv AND e.aciertos = t1.maximo
GROUP BY e.nivel) t2

ON e2.tiempo = t1.minimo
AND e2.nivel = e.nivel
AND e2.aciertos = t1.maximo

Un saludo y mil gracias de nuevo.

Rafalin.

[rafaelincl68]

En el que has puesto ya corregido, da un error en t1.niv, del mismo tipo, columna desconocida ...

Vaya tela con la consultita ...

[jurena]

Prueba esta:

SELECT e2.id_ensayo, e2.tiempo, e2.nivel,e2.aciertos from ensayos e2 inner join (SELECT e.nivel nivelf, min(e.tiempo)minimof, t1.maximo maximof
FROM ensayos e
INNER JOIN (SELECT nivel niv, max(aciertos) maximo FROM ensayos
GROUP BY nivel) t1 ON e.nivel = t1.niv AND e.aciertos = t1.maximo group by e.nivel)t2 on e2.nivel=t2.nivelf AND e2.tiempo = t2.minimof AND t2.maximof = e2.aciertos

He hecho alguna prueba con pocos datos. Te advierto que si dos o más del mismo nivel tienen el mismo número máximo de aciertos en el menor tiempo (coincidencia de tiempo), volverás a tener más de uno para cada nivel. Pero eso ya te lo podías imaginar

[rafaelincl68]

Ahora si que ha funcionado , muchas gracias amigo.

El tema de cuando ademas de coincidir aciertos y tiempo en el mismo nivel, ya lo habia contemplado, pues en ese caso habra dos o mas mejores resultados en un nivel

Y nada compañero, muchisimas gracias por todo, es un placer encontrar gente como tu con ganas de ayudar.

Saludos.

Rafalin.