"group by desc" syntax error on mysql 8.0 which is fine on 5.7

Question

The statement is like SELECT * FROM db.table group by id desc;

Would raise an error like

15:02:24 SELECT * FROM db.table group by id desc LIMIT 0, 10 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc LIMIT 0, 10' at line 1 0.00014 sec

on MySQL 8.0.13 in Ubuntu 18.04 Desktop 64bit

which would be fine on MySQL 5.7 in Windows or CentOS or Ubuntu.

I know basically, the select statement is like.

SELECT statement... [WHERE condition | GROUP BY `field_name(s)` HAVING condition] ORDER BY `field_name(s)` [ASC | DESC];

So is this 5.7's problem not to issue the error?

Or something more complicated on SQL standard?

Answer 1

I have the same issue, so for MySQL 8, I used the sql like that:

SELECT * FROM db.table 
group by id 
order by id desc 

Answer 2

Taking from @P.Salmon's comment for the question.

If you look up the select statement in the manual http://dev.mysql.com/doc/refman/5.7/en/select.html you will see that up to 5.7 asc|desc are optional modifiers to the group by statement which are no longer present from 8.0.and if you look at the upgrade documentation https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-sql-changes This deprecation is documented.

Since this situation, @Linda Li's answer could be a good option.