Selecting each Nth row does not work

Question

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?

Are you aware of that you cannot run multiple queries with single `mysql_query()` function ? — Teneff, Jul 22 '13 at 12:52
`mysql_query()` is identical to `mysqli::query()` and they both don't allow you to use multiple sql statements — Teneff, Jul 22 '13 at 12:56
thanks! I tried it via mysql console client and everything works. I cant accept your comment. — feedc0de, Jul 22 '13 at 13:05
you can accept juergen's it avoids your problem with a single query — Teneff, Jul 22 '13 at 13:07

juergen d · Accepted Answer · 2013-07-22T12:55:20.783

1

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

SQLFiddle demo

edited Jul 22 '13 at 12:55

answered Jul 22 '13 at 12:46

juergen d

201,996
37
293
362

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 ' @x := @x + 1 as rank FROM realvalues, (SELECT @x := 0) t WHERE x MOD' at line 1 – feedc0de Jul 22 '13 at 12:50

Selecting each Nth row does not work

1 Answers1

SQLFiddle demo