ROW_NUMBER() in MySQL

Question

Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()?

For example:

SELECT 
    col1, col2, 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1

Then I could, for example, add a condition to limit intRow to 1 to get a single row with the highest col3 for each (col1, col2) pair.

Answer 1

MariaDB 10.2 is implementing "Window Functions", including RANK(), ROW_NUMBER() and several other things:

https://mariadb.com/kb/en/mariadb/window-functions/

Based on a talk at Percona Live this month, they are reasonably well optimized.

The syntax is identical to the code in the Question.

Answer 2

I would also vote for Mosty Mostacho's solution with minor modification to his query code:

SELECT a.i, a.j, (
    SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a

Which will give the same result:

+------+------+------------+
|    i |    j | row_number |
+------+------+------------+
|    1 |   11 |          1 |
|    1 |   12 |          2 |
|    1 |   13 |          3 |
|    2 |   21 |          1 |
|    2 |   22 |          2 |
|    2 |   23 |          3 |
|    3 |   31 |          1 |
|    3 |   32 |          2 |
|    3 |   33 |          3 |
|    4 |   14 |          1 |
+------+------+------------+

for the table:

+------+------+
|    i |    j |
+------+------+
|    1 |   11 |
|    1 |   12 |
|    1 |   13 |
|    2 |   21 |
|    2 |   22 |
|    2 |   23 |
|    3 |   31 |
|    3 |   32 |
|    3 |   33 |
|    4 |   14 |
+------+------+

With the only difference that the query doesn't use JOIN and GROUP BY, relying on nested select instead.

Answer 3

The rownumber functionality can't be mimicked. You might get the results you expect, but you'll most likely get disappointed at some stage. Here's what mysql documentation says:

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second: SELECT @a, @a:=@a+1, ...; However, the order of evaluation for expressions involving user variables is undefined.

Regards, Georgi.

Answer 4

SELECT 
    col1, col2, 
    count(*) as intRow
FROM Table1
GROUP BY col1,col2
ORDER BY col3 desc

Answer 5

From MySQL 8.0.0 and above you could natively use windowed functions.

1.4 What Is New in MySQL 8.0:

Window functions.

MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as RANK(), LAG(), and NTILE(). In addition, several existing aggregate functions now can be used as window functions; for example, SUM() and AVG().

ROW_NUMBER() over_clause :

Returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.

ORDER BY affects the order in which rows are numbered. Without ORDER BY, row numbering is indeterminate.

Demo:

CREATE TABLE Table1(
  id INT AUTO_INCREMENT PRIMARY KEY, col1 INT,col2 INT, col3 TEXT);

INSERT INTO Table1(col1, col2, col3)
VALUES (1,1,'a'),(1,1,'b'),(1,1,'c'),
       (2,1,'x'),(2,1,'y'),(2,2,'z');

SELECT 
    col1, col2,col3,
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1;

DBFiddle Demo

Answer 6

There is no funtion like rownum, row_num() in MySQL but the way around is like below:

select 
      @s:=@s+1 serial_no, 
      tbl.* 
from my_table tbl, (select @s:=0) as s;