0

This is the SQL syntax:

WITH max_budget(value) AS (SELECTMAX(budget) FROM department)
SELECT budget       
FROM department, max_budget
WHERE department.budget = max_budget.value;

I get this error:

ERROR 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
'max_budget(value) AS (SELECTMAX(budget) FROM department)SELECT budget FROM ' at line 1

How can i solve this problem?, help me guys, thanks.

4

MySQL doesn't support Common Table Expressions (CTE) so either your mysql tag is incorrect or you've pulled this syntax from nowhere.

To make this work with MySQL, you'll need to rewrite it as derived tables. Note, these sql fiddles use the question that was a duplicate of this as it was at least more interesting to answer.https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql

This is the SQL Server version but I guarantee whatever logic you're attempting could be simplified if you explained what you were attempting to do versus "code doesn't work"

This specific question suffers from two issues: The first and already addressed is that CTEs WITH don't exist in mysql.

The second is simply syntax. You have SELECTMAX instead of SELECT MAX

WITH max_budget(value) AS (SELECT MAX(budget) FROM department)
SELECT budget       
FROM department, max_budget
WHERE department.budget = max_budget.value;

The above is valid syntax for SQL Server and all DBMS that have implemented CTEs. The MySQL equivalent follows

SELECT
    *
FROM
    department
WHERE
    department.budget =
    (
        SELECT MAX(budget) FROM department
    );

This uses a correlated subquery which has the distinct advantage of working in SQL Server, MySQL, PostGres, etc.

You could also write it as a derived table:

SELECT
    department.*
FROM
    department 
  JOIN
    (
        SELECT MAX(budget) AS value FROM department
    )  max_budget
  ON 
    department.budget = max_budget.value ;  
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy