-1

Suppose I have a table employee:

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

Why does this query give me a syntax error?

with sub as (
select Salary from employee
order by Salary desc
limit 2
) 
select min(Salary) from sub;

The error message is

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 'sub as (
select Salary from employee
order by Salary desc
limit 2
)
select min(' at line 1

I tried reading the documentation for subquery, with clauses, and select syntax and could not figure out what I'm doing wrong. Does MySQL care about whitespace/new lines/capitalization in queries?

  • 1
    Please edit the question. Add the text of each error exactly as it comes from the query processor. – Michael Green Nov 14 at 3:59
  • 2
    What's the version of Mysql?.No support for CTE lower than 8.0 version.Both queries works in 8.0 but second query fails in 5.6 – Kumar Nov 14 at 4:17
  • 2
    with was introduced on MySQL V 8.0 +, so till 5.7 you can't use with instead try using a sub query. – James Nov 14 at 4:23
2

Nothing wrong with your query. I already tested in my mariadb database version 10.2.25 and it works Please check your mysql version. If you want to use mysql, please upgrade to mysql version 8 If you want to switch to mariadb, make sure version minimal 10.2

Not the answer you're looking for? Browse other questions tagged or ask your own question.