I would like to ask if MySQL syntax
GROUP BY ... ASC/DESC was removed or not? By this worklog task it should be removed but it seems that it is not truth. There are no details in which version it was applied first time.
By this db-fiddle demo it still works well on any MySQL version (but on 5.7 and 8.0 sql_mode
ONLY_FULL_GROUP_BY must be disabled or
ANY_VALUE() must be used to avoid error
ER_WRONG_FIELD_WITH_GROUP). I know that aim in this demo can be re-written using inner join and subquery but in my use case it is 5 times slower on table with 4.500.000 rows. Demo is simplified. My real use case is much similar to this fiddle.
MySQL doc says that results from
GROUP BY, used that way I used, are non-deterministic and I should not rely on it. But in my case result are always identical on any MySQL version (can be tested be switching version in db-fiddle). There ore only two requirements: engine must be InnoDB and I have to create index for columns used in
GROUP BY (both requirements are not problem - InnoDB and index is performance advantage). I'm using this solution because it is much faster then any other solution using inner join, subqueries etc.
However syntax is removed or not there is alternative which produces identical result. So
GROUP BY col1 ASC/DESC can be rewriten as
GROUP BY col1 ORDER BY col1 ASC/DESC. Which one is better to use in my case? Both are identical what about results and performance too.
Thanks for any advice.