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

1

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 seize the opportunity and narrow down this question 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 we want to convert the following native commands from MySQL V8 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 that?

    
asked by anonymous 13.10.2018 / 00:53

1 answer

1

Basically with variables:

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

If for some reason you can not run the separate SET, you can do this:

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


See working in SQL Fiddle .


If you need the virtual table numbering, subquery, or JOIN , it suffices for the variable on the other side (or inside the parentheses).

    
13.10.2018 / 01:12