Geek Answers Handbook

Nested VIEW ignores ORDER BY

A question similar to this: MySQL: subquery browsing in FROM article restriction

I have the following table 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

I want to create a VIEW that will return show_idfor the highest show_yearfor everyone show_type. Here's a nested query that works - returns 2 and 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 |
+---------+-----------+
*/

For reference only, I also tried the following query, which at first seemed natural to me, but in my case it turned out to be bad , as shown below:

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 |
+---------+------------------+
*/

Now create VIEW - based on the nested query above (first SELECT), the problem is that the view will not accept the subquery .

, . .

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;

GROUP BY :

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`;

, , . GROUPed ORDER :

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

?

P.S.: SQL : http://sqlfiddle.com/#!2/e506d4/5

+4
3

:

VIEW, show_id show_year show_type. , 2 3:

, , show_id show_id :

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

, :

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;

, , , "" , MySQL. MySQL, . select group by ( , , ):

, , , , , , .

EDIT:

:

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

II:

, show_type show_id , :

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
                        )
                 );
+4

. , .

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 |
+---------+-----------+-----------+
*/

NULL - , , show_year , show_type

: http://sqlfiddle.com/#!2/f28510/3

+1

VIEW, show_id show_year 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)

( .)

show_ids show_year 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)
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);

:

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`;

, show_id show_type 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`;

, ORDER BY GROUP BY VIEW?

SELECT, ORDER BY GROUP BY. , ORDER BY GROUP BY SELECT GROUP BY. GROUP BY SELECT , ORDER BY SELECT , .

But the order of the table or view named in the query is not preserved by calculating its result. So why do you want ORDER BY before GROUP BY? Why do you think your nested view question should return a show_year maximum for show_type?

0
source

More articles:


All Articles