“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 ‘asc’” error happens on desktop or scheduling

Issues
#1

Issue

When retrieving data from MySQL in Exploratory Desktop, or when running a schedule that includes a step to retrieve data from MySQL, the following error occurs and the process fails:

Error : 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 'asc'

Cause

After upgrading MySQL to version 8.0 or later, specifying ASC or DESC within the GROUP BY clause is no longer supported.

In older versions of MySQL, the following non-standard syntax was allowed. However, in MySQL 8.0 and later, it results in a syntax error:

GROUP BY sales_by_year ASC

This change aligns with the standard SQL specification, which clearly separates the roles of GROUP BY and ORDER BY.

Solution

Modify the SQL so that GROUP BY and ORDER BY are written separately.

Before:

GROUP BY sales_by_year ASC

After:

GROUP BY sales_by_year
ORDER BY sales_by_year ASC

By separating them as follows:

  • Use GROUP BY only for grouping
  • Use ORDER BY to control the sorting

the query will run successfully in MySQL 8.0 and later.