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

Question

I know this question already was made several times, but it's always in some complex context or teoricaly incomplete.

I'm looking for a general answer like a math formula to serve for me and for future searches too.

So, let's see the table bellow...

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 let's say that we want rewrite the the commands bellow for MySQL V5.7.

1 - ROW_NUMBER WITH ONE PARTITION BY

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

SOLUTION:

SELECT 
    col_a, 
    col_b, 
    @row_num := IF(@part1=prt_x, @row_num + 1, 1) AS R, 
    @part1 := prt_x

FROM tbl

CROSS JOIN (SELECT @row_num := 1, @part1 := NULL) AS tbl_aux

2 - ROW_NUMBER WITH TWO PARTITION BY

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

SOLUTION:

SELECT 
    col_a, 
    col_b, 
    @row_num := IF(@part1=prt_x AND @part2=prt_y, @row_num + 1, 1) AS ROW_NUMBER, 
    @part1 := prt_x,
    @part2 := prt_y

FROM tbl

CROSS JOIN (SELECT @row_num := 1, @part1 := NULL, @part2 := NULL) AS tbl_aux

3 - ROW_NUMBER WITH ONE ORDER BY

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

SOLUTION:

SELECT 
    col_a, 
    col_b, 
    @row_num := IF(@part1=prt_x AND @part2=prt_y, @row_num + 1, 1) AS ROW_NUMBER, 
    @part1 := prt_x,
    @part2 := prt_y

FROM tbl

CROSS JOIN (SELECT @row_num := 1, @part1 := NULL, @part2 := NULL) AS tbl_aux ORDER BY ord_u ASC

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

SOLUTION

SELECT 
    col_a, 
    col_b, 
    @row_num := IF(@part1=prt_x AND @part2=prt_y, @row_num + 1, 1) AS ROW_NUMBER, 
    @part1 := prt_x,
    @part2 := prt_y

FROM tbl

CROSS JOIN (SELECT @row_num := 1, @part1 := NULL, @part2 := NULL) AS tbl_aux ORDER BY ord_u ASC, ord_v ASC

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

SOLUTION:

SELECT 
    col_a, 
    col_b, 
    @row_num := IF(@part1=prt_x AND @part2=prt_y, @row_num + 1, 1) AS ROW_NUMBER, 
    @part1 := prt_x,
    @part2 := prt_y

FROM tbl

CROSS JOIN (SELECT @row_num := 1, @part1 := NULL, @part2 := NULL) AS tbl_aux ORDER BY ord_u ASC, RAND()

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

SOLUTION:

SELECT 
    col_a, 
    col_b, 
    @row_num := IF(@part1=prt_x AND @part2=prt_y, @row_num + 1, 1) AS ROW_NUMBER, 
    @part1 := prt_x,
    @part2 := prt_y

FROM tbl

CROSS JOIN (SELECT @row_num := 1, @part1 := NULL, @part2 := NULL) AS tbl_aux ORDER BY ord_u ASC, MOD(n1, n2)

My question isn't literally a duplicate. It's different in terms of simplicity of the question and of generalization. EDIT: And I have a joker in my question: that's the order by RAND(). This is very different from olders questions. — Jose Henrique, Oct 12 '18 at 19:32
@JohnConde that duplicated question does not implement `ROW_NUMBER() OVER(PARTITION BY prt_x)` correctly.. It implements `ROW_NUMBER() OVER(ORDER BY col_a)`... — Raymond Nijland, Oct 12 '18 at 19:50
Although I understand your point why this question is not a duplicate of what John has suggested, however, there is no point of reopening the question just to close vote it as too broad. The guidance for questions with multiple questions in them is to close them as too broad. You have to focus your question on one of the subquestions and you should also describe what you have tried so far. — Shadow, Oct 12 '18 at 20:44
Raymond Nijland, man, your fiddle is incredible! Thanks very much!!!! — Jose Henrique, Oct 12 '18 at 20:50
Hey @RaymondNijland, do you try your formula in another table? The variable ROW_NUMBER() in your SELECT doesn't increase. Is always equals to one. — Jose Henrique, Oct 12 '18 at 22:52
i've deleted that comment i've might have been on the wrong track with those.. The non-real data example might helpt into making those mistakes.. — Raymond Nijland, Oct 12 '18 at 23:20

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

0 Answers0