Trouble with SQL CTE

Question

Was writing a sql statment and the CTE query works but when wrapped it is giving me a syntax 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 'dateCTE (id, firstDate) AS ( select id, min(date' at line 1

With dateCTE (id, firstDate) AS (
select id, min(date)
from notes
where deletion_time is null
and signed_time is not null
group by 1)
select *
from dateCTE;

Shoot sorry the where clause was part of it, I just missed it while copying it to stackoverflow.

• Nico Haase: Where did you define dateCTE?
• drVenkman123: @nicoHaase what do you mean define dateCTE... is that not what the "with dateCTE(....)" does.
• Nico Haase: Sorry for confusing - I had better asked the other way around: what do you want to do with dateCTE? Shouldn't there be anything to be selected from that?
• drVenkman123: yea this was part of a larger query but when debugging with a simple select * from dateCTE; I got the syntax error
• Nico Haase: ....then please share the exact query that triggers your problem. In the given example, there is no from dateCTE

Answer 1

We are running 5.7 not 8 and this is just not supported.

Answer 2

Not sure why you need cte, only single select statement would help you :

select id, min(date)
from notes
where deletion_time is null and signed_time is not null
group by id;

Note : You need where clause to filter the nulls & calls to `cte immediate after declaration :

With dateCTE (id, firstDate) AS (
 select id, min(date)
 from notes
 where deletion_time is null and signed_time is not null
 group by id
 )
select *
from dataCTE;

• drVenkman123: thanks, this is part of a larger query and I was attempting to debug this section as it throws the syntax error ... it is also the first CTE that is defined.... i updated as I did have the where just forgot to copy to StackOverflow also was using a similar statement select * from dateCTE;

Answer 3

Two things. You are missing a where in the CTE. And you are missing a select. So:

With dateCTE (id, firstDate) AS (
 select id, min(date)
 from notes
 where deletion_time is null and signed_time is not null
 group by 1
 )
select *
from dataCTE;

Note: This assumes that your database supports group by 1.

• drVenkman123: yea sorry that was a fat finger mistake... i edited to reflect the where. Also I tested the query without the CTE wrapper and it worked so assuming that group by 1 is supported.