Invalid input: WITH is not valid syntax in this context

Question

So I have similar request

WITH customers_in_usa AS (
        SELECT 
           customerName, state
        FROM
           customers
        WHERE
           country = 'USA'
    ) SELECT 
        customerName
    FROM
        customers_in_usa
    WHERE
        state = 'CA'
    ORDER BY customerName;

But while writing it I found an error: "WITH Invalid input at this position" error_picture. Can you help me understand what's wrong with this code?

Answer 1

WITHcustomers_in_usaAS This is currently invalid MySQL code. MySQL will support CTE in MySQL version 8 in the future.

You can rewrite the SQL code, which should produce the same results.

SELECT 
    customerName
  , state
FROM 
   customers 
WHERE
   country = 'USA'
 AND
   state = 'CA'
ORDER BY
   customerName