MySQL Workbench reports "is not valid at this position for this server version" error

Question

For the following SQL query:

SELECT COUNT (distinct first_name) from actor;

I receive the following error message:

"SELECT" is not valid at this position for this server version, expecting: '(', WITH

I am a total newbie at SQL. How do I resolve this error?

I put the exact same line at another PC with the exact same schema and it worked fine.

Answer 1

Have you tried to run the query deleting the space between "COUNT" and the bracket? I run a similar query to yours on MYSQL 5.7 and it gives me an error, but without that space the query runs.

Let's try this:

SELECT COUNT(DISTINCT first_name) FROM actor;

Answer 2

I know this isn't the exact problem you stated, but this was the same error message I was getting. The message is so generic that it could be anything...

So, from one newbie to another:

For me, the error occurred when I nested one query within another. I had a ; at the end of the first query and forgot to take it out. That threw the error. Once I deleted the ; in the inner query and added one at the end of the new query the error resolved.

Error:

Select
From (....
    Select
    From
    Where
    Group by
    Order ;     <== offending ;
) as ...
Where
Group by
Order

No Error:

Select
From (....
    Select
    From
    Where
    Group by
    Order
) as ...
Where
Group by
Order ;   <== correct placement

Answer 3

Mine error resolved using 'db_name.' with table although I have already executed use 'db_name' command;

select * FROM db_name.table_name;

Answer 4

I hope whenever you start writing select you will see this error. I too got the same error and read all the comments tried differently but nothing worked out for me but finally sorted out the error.

you got this error because you may not have closed the previous code. For example, if you have already written a code before, you ended up with order by (or) limit command but not closed it using a semicolon (;).

If you try to write a new command select after the previous command without closing it with a semicolon, you may end up with this error.