SQL ROW_NUMBER gives error

Question

I need to order rows in MySQL and assign a number to each row according to that order. ORDER BY is working as intended but not ROW_NUMBER().

This works:

USE my_database;
SELECT
    id
    ,volume
    FROM my_table
    ORDER BY volume;

This does not work:

USE my_database;
SELECT
    id
    ,volume
    ,ROW_NUMBER() over(ORDER BY volume)
    FROM my_table
    ORDER BY volume;

I get this error message:

SELECT id ,volume ,ROW_NUMBER() over(ORDER BY volume) FROM my_table ORDER BY volume Error Code: 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 volume) FROM my_table ORDER BY vol' at line 4 0.000 sec

What am I doing wrong and how do I make it work?

I also tried RANK() and DENSE_RANK() which gives the same problem.

Answer 1

There are no such things as ROW_NUMBER() or RANK() in MySQL. Try the following :

USE my_database;
SET @row_number = 0; 
SELECT id
     , volume
     , @row_number := @row_number + 1 AS rank
FROM my_table
ORDER BY volume;