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.

Solution 1

The function ROW_NUMBER() does not exist in MySQL.

However, you can replicate it, possibly: http://www.mysqltutorial.org/mysql-row_number/

The row_number is a ranking function that returns a sequential number of a row, starting from 1 for the first row. We often want to use the row_number function to produce the specific reports we need. Unfortunately, MySQL does not provide row_number like Microsoft SQL Server and Oracle. However, in MySQL, you can use session variables to emulate the row_number function.

example:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees
LIMIT 5;

Solution 2

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;

Comments

[anvandarnamn]

This didn't work but it was close to the solution: SET @row_number = 0; SELECT (@row_number:=@row_number + 1) AS rank, id, volume . Thanks!

[anvandarnamn]

Thank you! The solution was SET @row_number = 0; SELECT (@row_number:=@row_number + 1) AS rank, id, volume .

[Eric Korolev]

You have an error in variable name. Use row_number and then use rownumber without underscore.

[Michael]

Hello from the future. There IS such a thing a ROW_NUMBER starting in MySQL 8 and I'm on MySQL 14, but I still get the same error!