update - order by mysql w3school

La vista anidada ignora ORDER BY

Question

Pregunta similar a esta: MySQL: Ver con subconsulta en la limitación de la cláusula FROM

Tengo la siguiente tabla de shows :

DROP TABLE IF EXISTS `shows`;
CREATE TABLE `shows` (
  `show_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `show_type` int(11) unsigned DEFAULT NULL,
  `show_year` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`show_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `shows` VALUES
(NULL, 1, 2014), -- 1
(NULL, 1, 2015), -- 2
(NULL, 2, 2015), -- 3
(NULL, 2, 2014); -- 4

Quiero crear una VISTA que devuelva show_id para el show_year más show_year para cada show_type . Aquí hay una consulta anidada que funciona : devuelve 2 y 3:

SELECT s.show_id, s.show_year
FROM ( -- subquery for applying ORDER BY before GROUP BY
    SELECT *
    FROM shows
    ORDER BY show_year DESC
) s
GROUP BY show_type;
/*
+---------+-----------+
| show_id | show_year |
+---------+-----------+
|       2 |      2015 |
|       3 |      2015 |
+---------+-----------+
*/

Solo para la referencia también intenté la siguiente consulta que me pareció natural al principio, pero terminó siendo mala en mi caso, como se muestra a continuación:

SELECT s.show_id, MAX(s.show_year)
FROM shows s
GROUP BY show_type;
/*
+---------+------------------+
| show_id | MAX(s.show_year) |
+---------+------------------+
|       1 |             2015 | <== show_id=1 does NOT have show_year=2015
|       3 |             2015 |
+---------+------------------+
*/

Ahora, al crear una VISTA, basada en la consulta anidada anterior (la primera SELECCIÓN), el problema es que una vista no aceptará una subconsulta .

Entonces, estoy usando dos vistas. Uno dentro de otro.

El primero simplemente ordena la tabla por show_year DESC:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `shows_desc` AS
SELECT `s1`.`show_id` AS `show_id`,
    `s1`.`show_type` AS `show_type`,
    `s1`.`show_year` AS `show_year`
FROM `shows` `s1`
ORDER BY `s1`.`show_year` DESC;

El segundo se supone que debe hacer el GRUPO BY en el primero:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `shows_grouped` AS
SELECT `s2`.`show_id` AS `show_id`,
    `s2`.`show_year` AS `show_year`
FROM `shows_desc` `s2`
GROUP BY `s2`.`show_type`;

Sin embargo, para mi sorpresa, devuelve filas incorrectas. Como si AGRUPARA ignorando el PEDIDO de la subvista:

+---------+-----------+
| show_id | show_year |
+---------+-----------+
|       3 |      2015 |
|       1 |      2014 | <== why?
+---------+-----------+

Pistas?

PD: violín de SQL para empujar: http://sqlfiddle.com/#!2/e506d4/5

Answer 1

Quiero crear una VISTA que devuelva show_id para el show_year más alto para cada show_type.

select s.show_id
from shows s
where s.show_year=
    (select max(st.show_year)
    from shows st
    where st.show_type=s.show_type)

(Como en la respuesta de Gordon Linoff .)

En general, puede haber muchos show_ids con el máximo show_year de un show_type determinado. (Como también señaló en un comentario).

Si desea devolver solo uno de esos, dígalo y diga cuál en su pregunta. Para el máximo:

select s.show_id
from shows s
where s.show_year=
    (select max(st.show_year)
    from shows st
    where st.show_type=s.show_type)
and s.show_id=
    (select max(st.show_id)
    from shows st
    where st.show_type=s.show_type
    and st.show_year=s.show_year);

Usar vistas anidadas para los ID con el año máximo por tipo:

CREATE VIEW `shows_1` AS
SELECT `show_type` AS `show_type`,
    MAX(`show_year`) AS `show_year`
FROM `shows`
GROUP BY `show_type`;

CREATE VIEW `shows_ids` AS
SELECT `s`.`show_id`
FROM `shows` `s`
JOIN `shows_1` `s1`
ON `s`.`show_type`=`s1`.`show_type`
AND `s`.`show_year`=`s1`.`show_year`;

O si desea el máximo show_id por show_type y maximum show_year:

CREATE VIEW `shows_id` AS
SELECT MAX(`s`.`show_id`) AS `show_id`
FROM `shows` `s`
JOIN `shows_1` `s1`
ON `s`.`show_type`=`s1`.`show_type`
AND `s`.`show_year`=`s1`.`show_year`
GROUP BY `s`.`show_type`,`s`.`show_year`;

Entonces, ¿cómo se puede aplicar ORDER BY antes de GROUP BY en una VISTA?

Si desea ordenar el resultado de un SELECT, debe hacerlo en su orden BY después de GROUP BY. Entonces, para ORDER BY antes de agrupar, debe usar un SELECT externo con un GROUP BY. Si el externo GROUP BY SELECT está en una vista, entonces el innner ORDER BY SELECT puede tener que estar en otra vista nombrada en la primera.

Pero el orden de una tabla o vista nombrada en una consulta no se mantiene calculando su resultado. Entonces, ¿por qué quieres ORDENAR antes de GROUP BY? ¿Por qué crees que las vistas anidadas de tu pregunta deberían mostrar un máximo de show_year por show_type?

Answer 2

Esta consulta funcionó como una VISTA para mí. Espero que ayude a cualquiera.

SELECT s3.*
FROM shows s3
WHERE s3.show_id IN (
  SELECT s1.show_id
  FROM shows s1
      LEFT JOIN shows s2
      ON s1.show_type = s2.show_type
      AND s2.show_year > s1.show_year
  WHERE s2.show_id IS NULL
  GROUP BY s1.show_type
);
/*
+---------+-----------+-----------+
| show_id | show_type | show_year |
+---------+-----------+-----------+
|       2 |         1 |      2015 |
|       3 |         2 |      2015 |
+---------+-----------+-----------+
*/

WHERE IS NULL: significa que no hay ningún registro que show_year HIGHER más que este para cada show_type

El esquema completo y cada consulta se muestran aquí: http://sqlfiddle.com/#!2/f28510/3

Answer 3

Lew, concéntrate en esto:

Quiero crear una VISTA que devuelva show_id para el show_year más alto para cada show_type. Aquí hay una consulta anidada que funciona: devuelve 2 y 3:

Aquí hay una forma, suponiendo que el show_id incremente, de modo que el show_id más show_id encuentre en el último año:

select show_type, max(show_year) as show_year, max(show_id)
from shows
group by show_type;

Si no, intente esto:

select show_type, max(show_year) as show_year,
       substring_index(group_concat(show_id order by show_year desc), '','', 1) as show_id
from shows
group by show_type;

La razón por la que tus otras consultas no funcionan es porque tienes una comprensión "deseosa" de cómo funciona MySQL. Está utilizando una extensión para MySQL que explícitamente dice que no funciona en la documentación . Es decir, las columnas en la select no están en funciones de agregación y no están en la cláusula group by (y no son funcionalmente dependientes, pero ese es un concepto muy avanzado que no es relevante para esta discusión):

En este caso, el servidor puede elegir libremente cualquier valor de cada grupo, de modo que, a menos que sean iguales, los valores elegidos son indeterminados, lo que probablemente no sea lo que usted desea.

EDITAR:

Lo siguiente también debería funcionar para una vista:

select s.*
from shows s
where s.show_year = (select max(s2.show_year) from shows s2 where s2.show_type = s.show_type);

EDIT II:

Si solo quieres una fila para cada show_type y show_id es única, entonces debería funcionar:

select s.*
from shows
where not exists (select 1
                  from shows s2
                  where s2.show_type = s.show_type and
                        (s2.show_year > s.show_year or
                         s2.show_year = s.show_year and s2.show_id > s.show_id
                        )
                 );