How to emulate ROW_NUMBER() in Mysql V5.7?

Question

I know this is a common and well-documented issue in English, but there is little of it in Portuguese. So I’m going to take this opportunity and take this doubt in a simple and comprehensive way.

Let’s assume that we will work with the table below:

col_a  | col_b  | prt_x  | prt_y  | ord_u  | ord_w
-------+--------+--------+--------+--------+--------
row_a1 | row_b1 | row_x1 | row_y1 | row_u1 | row_w1
row_a2 | row_b2 | row_x2 | row_y2 | row_u2 | row_w2
row_a3 | row_b3 | row_x3 | row_y3 | row_u3 | row_w3
row_a4 | row_b4 | row_x4 | row_y4 | row_u4 | row_w4

And that we want to convert the following native Mysql V8 commands to V5.7

1 - ROW_NUMBER WITH A PARTITION BY

SELECT col_a, col_b, ROW_NUMBER() OVER(PARTITION BY prt_x)
FROM tbl

2 - ROW_NUMBER WITH TWO PARTITION BY

SELECT col_a, col_b, ROW_NUMBER() OVER(PARTITION BY prt_x, prt_y)
FROM tbl

3 - ROW_NUMBER WITH AN ORDER BY

SELECT col_a, col_b, ROW_NUMBER() OVER(PARTITION BY prt_x, prt_y, ORDER BY ord_u)
FROM tbl

4 - ROW_NUMBER WITH TWO ORDER BY

SELECT col_a, col_b, ROW_NUMBER() OVER(PARTITION BY prt_x, prt_y, ORDER BY ord_u, ord_v)
FROM tbl

5 - ROW_NUMBER WITH ORDER BY RAND

SELECT col_a, col_b, ROW_NUMBER() OVER(PARTITION BY prt_x, prt_y, ORDER BY ord_u, RAND())
FROM tbl

6 - ROW_NUMBER WITH ORDER BY MOD

SELECT col_a, col_b, ROW_NUMBER() OVER(PARTITION BY prt_x, prt_y, ORDER BY ord_u, MOD(n1, n2))
FROM tbl

So how could we do this?

Answer 1

Basically with variables:

SET @linha := 0;
SELECT campo1, campo2, @linha := @linha + 1 AS row_number FROM tabela;

If for some reason you cannot run the separate SET, you can do so:

SELECT campo1, campo2, @linha := @linha + 1 AS row_number FROM tabela, (SELECT @linha := 0) l;

See working on SQL Fiddle.

Still, if you need the numbering of the virtual table, subquery, or JOIN, simply put the variable on the other side (or inside the parentheses).