Complex MySQL cmd with JOINS and Counters doesnt work

Question

I am working on this SQLFiddle and cant get the command working. Here the command:

SET @n := 1;
SET @start := '2013-07-22 10:00:01';
SET @end := '2013-07-22 10:00:02';
SET @register := 40001;

SELECT * FROM 
    (
    SELECT
        `realvalues`.`Timestamp`,
        `realvalues`.`Value` * `register`.`Factor`,
        @x := @x + 1 AS rank
    FROM
        `realvalues`,
        (SELECT @x := 0) t            
    WHERE
        `realvalues`.`Register` = register AND
        `realvalues`.`Timestamp` BETWEEN start AND end
    JOIN
        `register`
    ON
        `register`.`DeviceID` = `realvalues`.`DeviceID` AND
        `register`.`Register` = `realvalues`.`Register`
    ) a
WHERE
    rank MOD ? = n

Does anybody know where the command fails? MySQL error reporting isnt very usefull.

[EDIT] The Value is now duplicated with Factor.

Answer 1

There are many many things wrong with your query. However, the error that is being reported in your fiddle is:

...check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN start AND end JOIN register ON ...

Your syntax for BETWEEN is incorrect. There should be no IS token before BETWEEN. Correct syntax is:

<value> BETWEEN <lower-bound-inclusive> AND <upper-bound-inclusive>

Other problems include:

• start, end, and n are not columns
• register (in the WHERE clause) is ambiguous
• You have a JOIN clause after a WHERE clause
• You do not specify an alias for the second column of your derived table a (perhaps not necessary but may cause issues)
• Use of a ? parameter without a way to specify a value (although this is a limitation of SQL Fiddle and not necessarily a problem with your SQL statement)