[Ayuda][Mysql][Avanzado] Registos unicos y columnas calculadas

[nosoul]

Hola, disculpen.
tengo una consulta que me esta rompiendo la cabeza.
Resulta que tengo unos registros
con un codigo, tamaño,id y nombre.
Nesecito por medio de una consulta restarle el tamaño por uno ingresado y que me devuelva el resultado que mas se acerque a otro valor tambien ingresado(en el ejemplo es 200), ademas nesecito que los codigos no se repitan, es decir me devuelva el mas aproximado por codigo.
El problema es que me devuelve bien la diferencia pero no logro que me devuelva solo 1 por codigo.

RESUMIDO SERIA:
para cada código, el id correspondiente para el registro con menor diferencia

Código PHP:

  select Nombre, codigo, (tamaño-400) as diferencia 
from objetos 
having diferencia<= 200 
order by diferencia asc  
   

Lo que esta consulta me devuelve es:
Id-----Nombre---Codigo---Diferencia
5-----Pelota1------100------ 30
3-----Pelota2------100------40
2-----Carpa1-------200------45
1-----Pelota3------100------ 60


Y lo que necesito es que me devuelva:

Id-----Nombre -----Codigo ---Diferencia
5-----Pelota1 ------100---------30
2-----Carpa1------- 200---------45


Distinct y group by codigo no me estan funcionando.
El group me esta devolviendo

Id-----Nombre -----Codigo ---Diferencia
1-----Pelota3 ------100---------60
2-----Carpa1------- 200---------45


Agradesco toda su ayuda!

[Gustavo72]

Probá usando:

SELECT DISTINCT(codigo), ... FROM ... WHERE ... GROUP BY codigo

[nosoul]

Sucede lo mismo que en el ejemplo que yo puse cuando lo uso.
Si uso group by,
me devuelve los primeros Id de objeto que encuentra con los codigos diferentes y el ordenamiento por la menor diferencia no tiene efecto

[Gustavo72]

Iniciado por nosoul

Sucede lo mismo que en el ejemplo que yo puse cuando lo uso.
Si uso group by,
me devuelve los primeros Id de objeto que encuentra con los codigos diferentes y el ordenamiento por la menor diferencia no tiene efecto

pero el HAVING y el ORDER BY no los tenés que eliminar... Sería así:

Código MySQL:

SELECT DISTINCT(codigo), Nombre, (tamaño-400) as diferencia
FROM objetos HAVING diferencia<= 200 GROUP BY codigo ORDER BY diferencia
 

Y si no funciona probá esto otro:

Código MySQL:

SELECT DISTINCT(codigo), Nombre, diferencia FROM 
(SELECT codigo, Nombre, (tamaño-400) as diferencia FROM objetos HAVING diferencia<=200 ORDER BY diferencia) AS t 
GROUP BY codigo ORDER BY diferencia
 

[leonardo_josue]

Hola nosoul:

Hay un problema con el código de Gustavo72, para este caso la cláusula DISTINCT no sirve pues hay que recordar que cuando se lista más de un campo SE EXCLUYEN SÓLO AQUELLOS REGISTROS DONDE TODOS LOS CAMPOS SEAN IGUALES, no sólo uno. Es por eso que vuelven a aparecer los mismos registros. Veamos si este ejemplo les deja más claro el problema:

Código MySQL:

mysql> SELECT id, nombre  FROM tabla;
+------+----------+
| id   | nombre   |
+------+----------+
|    1 | uno      |
|    1 | uno      |
|    1 | otro uno |
|    2 | uno      |
+------+----------+
4 rows in set (0.08 sec)
 
mysql> SELECT DISTINCT id  FROM tabla;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
 
mysql> SELECT DISTINCT nombre  FROM tabla;
+----------+
| nombre   |
+----------+
| uno      |
| otro uno |
+----------+
2 rows in set (0.00 sec)
 
mysql> SELECT DISTINCT id, nombre  FROM tabla;
+------+----------+
| id   | nombre   |
+------+----------+
|    1 | uno      |
|    1 | otro uno |
|    2 | uno      |
+------+----------+
3 rows in set (0.01 sec)
 
mysql> SELECT DISTINCT (id), nombre  FROM tabla;
+------+----------+
| id   | nombre   |
+------+----------+
|    1 | uno      |
|    1 | otro uno |
|    2 | uno      |
+------+----------+
3 rows in set (0.00 sec)
 

Observen en el último caso poner entre paréntesis el campo id es exactamente lo mismo que no ponerlo, pues como dije anteriormente la comparación se hace sobre la totalidad de los campos. Es por eso que cuando se incluyen los dos campos sólo se excluye el id=1 con nombre=uno pues este SI ESTÁ DUPLICADO.

Ahora bien, con respecto al problema original, no me queda del todo claro qué es lo que quieres hacer, pero según el planteamiento veamos si esto te sirve.

Supongamos que tenemos estos datos de ejemplo:

Código MySQL:

mysql> SELECT * FROM tabla;
+------+---------+--------+---------+
| id   | nombre  | codigo | tamanio |
+------+---------+--------+---------+
|    1 | Pelota3 |    100 |     420 |
|    2 | Carpa1  |    200 |     445 |
|    3 | Pelota2 |    100 |     440 |
|    4 | Carpa2  |    200 |     340 |
|    5 | Carpa3  |    100 |     350 |
|    6 | Pelota4 |    100 |     380 |
+------+---------+--------+---------+
6 rows in set (0.00 sec)
 

Si quisiéramos obtener aquellos productos PARA CADA CATEGORÍA con la menor diferencia con respecto a un tamaño de 400 tendríamos que hacer el siguiente procedimiento (espero no extenderme mucho en el planteamiento, pero quiero dejarles lo más claro posible la lógica de la consulta, pues este tipo de consultas estoy seguro que les servirá en el futuro). Si obtenemos las diferencias con respecto a un tamaño base de 400 obtenemos lo siguiente:

Código MySQL:

mysql> SELECT id, nombre, codigo, tamanio, tamanio-400 diferencia FROM tabla;
+------+---------+--------+---------+------------+
| id   | nombre  | codigo | tamanio | diferencia |
+------+---------+--------+---------+------------+
|    1 | Pelota3 |    100 |     420 |         20 |
|    2 | Carpa1  |    200 |     445 |         45 |
|    3 | Pelota2 |    100 |     440 |         40 |
|    4 | Carpa2  |    200 |     340 |        -60 |
|    5 | Carpa3  |    100 |     350 |        -50 |
|    6 | Pelota4 |    100 |     380 |        -20 |
+------+---------+--------+---------+------------+
6 rows in set (0.00 sec)
 

Observen el primer detalle que quiero hacer notar, LAS DIFERENCIAS PUEDEN SER TANTO POSITIVAS COMO NEGATIVAS, es decir tamaños más grandes o más pequeños con respecto a la base. si no interesa en realidad el signo pueden hacer uso de la función ABS para manejar absolutos. Ahora bien, como quieren obtener las mínimas diferencias POR CATEGORÍA pueden hacer algo como esto:

Código MySQL:

mysql> SELECT codigo, MIN(ABS(tamanio-400)) diferencia FROM tabla
    -> GROUP BY codigo;
+--------+------------+
| codigo | diferencia |
+--------+------------+
|    100 |         20 |
|    200 |         45 |
+--------+------------+
2 rows in set (0.00 sec)
 

Ahora bien, esa información ES LA QUE DEBEN TOMAR EN CUENTA PARA FILTRAR LA INFORMACIÓN. Una manera de hacerlo (que no la única y posiblemente no la mejor) sería haciendo esto:

Código MySQL:

mysql> SELECT T1.* FROM tabla T1
    -> INNER JOIN
    -> (SELECT codigo, MIN(ABS(tamanio-400)) diferencia
    -> FROM tabla GROUP BY codigo) T2
    -> ON T1.codigo = T2.codigo AND ABS(T1.tamanio - 400) = T2.diferencia
    -> ORDER BY codigo;
+------+---------+--------+---------+
| id   | nombre  | codigo | tamanio |
+------+---------+--------+---------+
|    1 | Pelota3 |    100 |     420 |
|    6 | Pelota4 |    100 |     380 |
|    2 | Carpa1  |    200 |     445 |
+------+---------+--------+---------+
3 rows in set (0.03 sec)
 

Observen que para el código 100 aparecen dos registros PUES AMBOS TIENEN UNA DIFERENCIA DE 20 UNA HACIA ARRIBA Y OTRA HACIA ABAJO. Para el caso de del codigo 200 sólo muestra uno, pues los otros registros tienen una diferencia de 50 y 60 respectivamente.

Espero que con este ejercicio puedas obtener tu consulta, cualquier duda la comentas en el foro

Saludos
Leo.

[Gustavo72]

Iniciado por leonardo_josue

SE EXCLUYEN SÓLO AQUELLOS REGISTROS DONDE TODOS LOS CAMPOS SEAN IGUALES

Lo que pasa es que te olvidaste de ponerle GROUP BY id a todas las pruebas que hiciste.
Probalo y vas a ver que sí funciona.

[nosoul]

Iniciado por leonardo_josue

Hola nosoul:

Hay un problema con el código de Gustavo72, para este caso la cláusula DISTINCT no sirve pues hay que recordar que cuando se lista más de un campo SE EXCLUYEN SÓLO AQUELLOS REGISTROS DONDE TODOS LOS CAMPOS SEAN IGUALES, no sólo uno. Es por eso que vuelven a aparecer los mismos registros. Veamos si este ejemplo les deja más claro el problema:

Código MySQL:

mysql> SELECT id, nombre  FROM tabla;
+------+----------+
| id   | nombre   |
+------+----------+
|    1 | uno      |
|    1 | uno      |
|    1 | otro uno |
|    2 | uno      |
+------+----------+
4 rows in set (0.08 sec)
 
mysql> SELECT DISTINCT id  FROM tabla;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
 
mysql> SELECT DISTINCT nombre  FROM tabla;
+----------+
| nombre   |
+----------+
| uno      |
| otro uno |
+----------+
2 rows in set (0.00 sec)
 
mysql> SELECT DISTINCT id, nombre  FROM tabla;
+------+----------+
| id   | nombre   |
+------+----------+
|    1 | uno      |
|    1 | otro uno |
|    2 | uno      |
+------+----------+
3 rows in set (0.01 sec)
 
mysql> SELECT DISTINCT (id), nombre  FROM tabla;
+------+----------+
| id   | nombre   |
+------+----------+
|    1 | uno      |
|    1 | otro uno |
|    2 | uno      |
+------+----------+
3 rows in set (0.00 sec)
 

Observen en el último caso poner entre paréntesis el campo id es exactamente lo mismo que no ponerlo, pues como dije anteriormente la comparación se hace sobre la totalidad de los campos. Es por eso que cuando se incluyen los dos campos sólo se excluye el id=1 con nombre=uno pues este SI ESTÁ DUPLICADO.

Ahora bien, con respecto al problema original, no me queda del todo claro qué es lo que quieres hacer, pero según el planteamiento veamos si esto te sirve.

Supongamos que tenemos estos datos de ejemplo:

Código MySQL:

mysql> SELECT * FROM tabla;
+------+---------+--------+---------+
| id   | nombre  | codigo | tamanio |
+------+---------+--------+---------+
|    1 | Pelota3 |    100 |     420 |
|    2 | Carpa1  |    200 |     445 |
|    3 | Pelota2 |    100 |     440 |
|    4 | Carpa2  |    200 |     340 |
|    5 | Carpa3  |    100 |     350 |
|    6 | Pelota4 |    100 |     380 |
+------+---------+--------+---------+
6 rows in set (0.00 sec)
 

Si quisiéramos obtener aquellos productos PARA CADA CATEGORÍA con la menor diferencia con respecto a un tamaño de 400 tendríamos que hacer el siguiente procedimiento (espero no extenderme mucho en el planteamiento, pero quiero dejarles lo más claro posible la lógica de la consulta, pues este tipo de consultas estoy seguro que les servirá en el futuro). Si obtenemos las diferencias con respecto a un tamaño base de 400 obtenemos lo siguiente:

Código MySQL:

mysql> SELECT id, nombre, codigo, tamanio, tamanio-400 diferencia FROM tabla;
+------+---------+--------+---------+------------+
| id   | nombre  | codigo | tamanio | diferencia |
+------+---------+--------+---------+------------+
|    1 | Pelota3 |    100 |     420 |         20 |
|    2 | Carpa1  |    200 |     445 |         45 |
|    3 | Pelota2 |    100 |     440 |         40 |
|    4 | Carpa2  |    200 |     340 |        -60 |
|    5 | Carpa3  |    100 |     350 |        -50 |
|    6 | Pelota4 |    100 |     380 |        -20 |
+------+---------+--------+---------+------------+
6 rows in set (0.00 sec)
 

Observen el primer detalle que quiero hacer notar, LAS DIFERENCIAS PUEDEN SER TANTO POSITIVAS COMO NEGATIVAS, es decir tamaños más grandes o más pequeños con respecto a la base. si no interesa en realidad el signo pueden hacer uso de la función ABS para manejar absolutos. Ahora bien, como quieren obtener las mínimas diferencias POR CATEGORÍA pueden hacer algo como esto:

Código MySQL:

mysql> SELECT codigo, MIN(ABS(tamanio-400)) diferencia FROM tabla
    -> GROUP BY codigo;
+--------+------------+
| codigo | diferencia |
+--------+------------+
|    100 |         20 |
|    200 |         45 |
+--------+------------+
2 rows in set (0.00 sec)
 

Ahora bien, esa información ES LA QUE DEBEN TOMAR EN CUENTA PARA FILTRAR LA INFORMACIÓN. Una manera de hacerlo (que no la única y posiblemente no la mejor) sería haciendo esto:

Código MySQL:

mysql> SELECT T1.* FROM tabla T1
    -> INNER JOIN
    -> (SELECT codigo, MIN(ABS(tamanio-400)) diferencia
    -> FROM tabla GROUP BY codigo) T2
    -> ON T1.codigo = T2.codigo AND ABS(T1.tamanio - 400) = T2.diferencia
    -> ORDER BY codigo;
+------+---------+--------+---------+
| id   | nombre  | codigo | tamanio |
+------+---------+--------+---------+
|    1 | Pelota3 |    100 |     420 |
|    6 | Pelota4 |    100 |     380 |
|    2 | Carpa1  |    200 |     445 |
+------+---------+--------+---------+
3 rows in set (0.03 sec)
 

Observen que para el código 100 aparecen dos registros PUES AMBOS TIENEN UNA DIFERENCIA DE 20 UNA HACIA ARRIBA Y OTRA HACIA ABAJO. Para el caso de del codigo 200 sólo muestra uno, pues los otros registros tienen una diferencia de 50 y 60 respectivamente.

Espero que con este ejercicio puedas obtener tu consulta, cualquier duda la comentas en el foro

Saludos
Leo.

Muchas gracias a todos,
Y leonardo, Gracias a ti por iluminarme con tu conocimiento, nisiquiera concidere condicionar el join para obtener ese resultado.
Funciono de maravillas y realmente me siento muy tranquilo ahora que lo pude arreglar efecientemente, GRACIAS A TI.

Al resto, muchisimas gracias por su predisposicion y estar ahi!

[nosoul]

Una consulta adicional. no puedo usar en una clausula ON de un INNER JOIN una columna calculada?
para ecitarme volver a hacer la cuenta, poque si en ves de una resta, es una susecion de multiplicaciones restada por otras multiplicaciones, gasto bastantes recursos