Syntax Error trying to return row number from database

Question

SELECT ROW_NUMBER()OVER(ORDER BY id), x, y, id
FROM coordonnees
ORDER BY id

I'm trying to return the row number of each row (the table named coordonnees with 3 columns: id, x, y), but this code doesn't work

The error is:

Syntax error near '(ORDER BY id), x, y, id

on database : MYSQL 5.7.9

Answer 1

Apparently, MySQL 5.7 doesn't support ROW_NUMBER(). However, I found a novel solution that will emulate it at this site.

SELECT  @row_num := IF(@prev_value=c.id,@row_num+1,1) AS RowNumber
       ,c.x
       ,c.y
       ,c.id
       ,@prev_value := c.id
FROM coordonnees c,
    (SELECT @row_num := 1) x,
    (SELECT @prev_value := -1) y
ORDER BY c.id

There are a few caveats, though:

• The @row_num variable must be set before the @prev_value variable.
• The first field in the ORDER BY must be the field that you are partitioning by.
• The default value assigned to the @prev_value variable must not exist in the partition by field.