Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I try to run the following sql query in mysql

SET @rank:=0;
WITH TEMP
(
  SELECT  @rank := @rank + 1 AS ROW_ID , UUID() as RANDOM_VALUE,D.*
        FROM   house_information_new D
       ORDER BY RANDOM_VALUE
)

SELECT 'Training',T.* FROM TEMP T
WHERE ROW_ID <= 375
UNION
SELECT 'Test',T.* FROM TEMP T
WHERE ROW_ID > 375

However I get this error #1064 - 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 'TEMP ( SELECT @rank := @rank + 1 AS ROW_ID , UUID() as RANDOM_VALUE,D.* ' at line 1

but I do not find anything I do wrong.

Can anyone give me a hand to this?

Thanks in advance

share|improve this question

closed as off topic by JNK May 7 '13 at 12:43

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
I had the recollection that MySQL didn't have CTEs. Did that change? (Please include your MySQL version.) –  Mat May 7 '13 at 10:23
    
Please don't post basic questions here. I realize you can't ask questions on SO any more, but that doesn't mean you can post off topic posts here. –  JNK May 7 '13 at 12:44

1 Answer 1

up vote 2 down vote accepted

MySQL does not have CTEs. You can (and should) rewrite the query using either a temporary table or a nested subquery (derived table):

SET @rank:=0;

SELECT CASE WHEN row_id <= 375 
           THEN 'Training'
           ELSE 'Test'
       END AS SomeAlias, 
       t.* 
FROM 
  ( SELECT   @rank := @rank + 1 AS row_id, UUID() as random_value, d.*
    FROM     house_information_new AS d
    ORDER BY random_value
  ) AS t ;
share|improve this answer
    
Thank you! This was what I wanted. Thanks again. Choosing as best answer –  Andreas May 7 '13 at 13:59

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