[Fix] error: 1064 (42000): You have an error in your SQL syntax

The Error “error: 1064 (42000): You have an error in your SQL syntax;”, is an error that is invoked when you are working on MySQL and are trying to use the world.sql schema to display your query in a descending way.

Given below is the snippet of the error you might get:

1064 (42000): 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'

Given below is the detailed solution as to why the error, “error: 1064 (42000): You have an error in your SQL syntax;” is seen and how to fix the error.

The error, “error: 1064 (42000): You have an error in your SQL syntax;” is seen because, before MySQL 8.0.13, MySQL provided an unconventional syntax modification that used to allow explicit ASC or DESC designators for GROUP BY columns.

The GROUP BY extension is no longer supported now as of MySQL 8.0.13 version; ASC or DESC designators for GROUP BY columns are not allowed. The link for the documentation of the above is given here.

MySQL 8.0.13 version States that:

MySQL 8.0.13 version offered a nonstandard syntax enhancement that allowed explicit ASC or DESC designators for GROUP BY columns prior to version 8.0.13. 
MySQL 8.0.12 and subsequent versions allow ORDER BY with grouping functions, eliminating the need for this addition.

(Bug #86312, Bug #26073525) This also implies that you may order on arbitrary columns when using GROUP BY, such as this:

SELECT a, b, COUNT(c) AS t FROM test_table GROUP BY a,b ORDER BY a,t DESC;

As of MySQL 8.0.13, the GROUP BY extension is no longer supported: ASC or DESC designators for GROUP BY columns are not permitted.

To fix the error, “error: 1064 (42000): You have an error in your SQL syntax;” you will have to replace the ‘la.language DESC’ in your code by another ‘la.Language code which works correctly and error would be fixed.

The GROUP BY extension is no longer supported as of MySQL 8.0.13; ASC or DESC designators for GROUP BY columns are also not allowed.

To fix the error “error: 1064 (42000): You have an error in your SQL syntax;”, you will have to follow the steps mentioned below:

Step 1: Select GROUP BY column code

First you will have to find the group by columns code snippet in your code which you have written.

Step 2: Replace with GROUP BY la.Language code

Then you will have to replace that by the code snippet which will be discussed below, this is because the GROUP BY extension is no longer supported as of MySQL 8.0.13; ASC or DESC designators for GROUP BY columns are not allowed.

You will have to replace the below mentioned code:

GROUP BY la.Language DESC;

with the code mentioned below:

GROUP BY la.Language 
ORDER BY la.Language DESC;

This should work well with any MySQL version which you are using in your in the system.

This should help you fix the error, “error: 1064 (42000): You have an error in your SQL syntax;”.

Conclusion

To fix the error, “error: 1064 (42000): You have an error in your SQL syntax;” you will have to replace the la.language DESC in your code by another appropriate code which works correctly and does not throw an error.

The GROUP BY extension is no longer supported as of MySQL 8.0.13; ASC or DESC designators for GROUP BY columns are not allowed