7

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.

potashin
  • 44,205
  • 11
  • 83
  • 107
anvandarnamn
  • 133
  • 1
  • 2
  • 8
  • Looks like duplicate question of: http://stackoverflow.com/questions/1895110/row-number-in-mysql – Lance Jan 24 '15 at 19:01
  • "MySQL introduced the ROW_NUMBER() function since version 8.0" (https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/) – Sameer Jul 06 '20 at 09:52

3 Answers3

4

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;
potashin
  • 44,205
  • 11
  • 83
  • 107
  • 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 Jan 24 '15 at 19:24
  • You have an error in variable name. Use row_number and then use rownumber without underscore. – Eric Korolev Feb 08 '19 at 09:54
  • 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! – Michael May 03 '22 at 22:57
3

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;
Lance
  • 638
  • 1
  • 6
  • 22
3

MySQL introduced the ROW_NUMBER() function since version 8.0.

Ferdi
  • 31
  • 1