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

user_1278112 0
Viewed 5673

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?

mysql-5.7 mysql-8.0 mysql sql
3 Answers

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
user_87745820

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.

user_12781120

This query makes no sense:

SELECT *
FROM db.table 
GROUP BY id DESC;

You are doing an aggregation query. So (presumably), the table has multiple rows per id. Those are condensed down to one row. What values should be used for the other columns? It is sad that MySQL ever supported this syntax. So a welcome change is that ONLY_FULL_GROUP_BY is now the default.

A small hint is that using an aggregation query with no aggregation functions is suspicious.

Perhaps you want:

select id, min(col1), min(col2), . . .
from t
group by id;

Or more likely, you want a particular row, such as the "earliest" or "most recent", something like:

select t.*
from t
where t.createdAt = (select min(t2.createdAt) from t t2 where t2.id = t.id);
user_11440350
Related