Search code examples
mysqlsqlfiddle

Selecting each Nth row does not work

I developed a SQL command in my SQLFiddle. MySQL Server is set to Version 5.1.61 because my server runs 5.5.31-0+wheezy1. I copied the Schema to my server and created a PHP script that executes the code (with help from here). But my server returns this:

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 'SELECT * FROM (SELECT (@x:=@x+1) AS x, `ID` FROM realvalues) t WHERE x MOD 3 =' at line 2

the SQL command:

SET @x := 0;
SELECT *
FROM (SELECT (@x:=@x+1) AS x, `ID` FROM realvalues) t
WHERE x MOD 3 = 0;

Why does it work on SQLFiddle and does not work on my server?

Solution 
  • select * from 
(
  SELECT id, 
         @x := @x + 1 as rank
  FROM realvalues, (SELECT @x := 0) t
) a
where rank mod 3 = 0

    SQLFiddle demo