MYSQL Select row_number() doesn't work

Question

I'm trying to get row 5 to 10 when sorting by date but for some reason I always end up with this sql 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 '(ORDER BY CreatedDate) AS Row, GameID, UserID, Title, Description, CreatedDate F' at line 1

This is the query I'm trying to use:

SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate) AS Row, GameID, UserID, Title, Description, CreatedDate
FROM game
WHERE Row >= 5 AND Row <= 10

Does someone know what I'm exactly doing wrong here? Or even a better way to solve this problem?

Thanks for reading/helping

mysql does not have row_number aggregate function. you can use mysql variables for this — amdixon, Nov 24 '15 at 10:44

score 1 · Accepted Answer · answered Nov 24 '15 at 10:47

1

There isn't a rownumber() function in MySQL. This is the closest you can get:

SELECT 
    @i:=@i+1 AS rank, 
    t.*
FROM 
    tblname AS t,
    (SELECT @i:=0) AS R

answered Nov 24 '15 at 10:47

Thanos Markou

2,587
3
25
32

this seems to work but when I try WHERE rank = 1 it ends up crashing again – user4598368 Nov 24 '15 at 10:59

MYSQL Select row_number() doesn't work

1 Answers1