"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?

Departmentalism

Without order by clause, desc does not make sense. And I suspect that this query should fail in version 5.7 too – Esmeraldaesmerelda

I had the same idea, but after try it the result really puzzled me. – Departmentalism

The error indicate a missing order by clause. DESC is used in the order by, not the group by. – Spruill

This should be treated as a bug, but I doubt it will be - in older versions of MySQL the group by clause responded to (i.e. permitted) ASC/DESC so it got used a short hand way of ordering the final result. see rextester.com/NJDD72740 – Hautbois

yeah, so I guess it would be a "hidden feature". – Departmentalism

yes, another one :) – Hautbois

If you look up the select statement in the manual 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 dev.mysql.com/doc/refman/8.0/en/… this deprecation is documented. – Candescent

Thank you @Candescent I missed that part. – Departmentalism

@Candescent could you make your comment as an answer? So we can mark the question as solved. – Departmentalism

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 

Piccoloist

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.

Departmentalism

Answer 3

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

Bronchoscope

The original query is something like select id,min(col1) ... , however my point is about the second part. – Departmentalism

It is fine that it is your personal opinion, that "not full group by" is bad. Actually this is a great feature which saves a lot of time for developers. It is well documented, that MySQL will pick a random value from the grouped rows. So if you know that all values are identical, this is a great shortcut. Other databases which do not support this feature make we want to jump out of the window when I run into this problem. Just my personal opinion: this feature should be in the next SQL standard. – Frumenty

@drfumanchu . . . Interesting that the designers of MySQL have removed this feature from MySQL 8.0 (at least using the default settings). Given that the designers of SQL also have left this functionality out and almost no other databases support such syntax, your opinion would seem to be a minority opinion. – Bronchoscope

ANY_VALUE() is now in the SQL:2023 standard, for better or for worse. It is not widely supported yet. modern-sql.com/caniuse/any_value – Liberalism

Answer 4

Clearly explained here WL#8693: Remove the syntax for GROUP BY ASC and DESC

https://dev.mysql.com/worklog/task/?id=8693

Flemish