I need to generate rank in mysql v5.7.31

See more:

MySQL

I need to create rank based on some column in the table. But in MySQL v5.7.31 it is not supporting the row_number()over() function.

I have used the below query, but it is not working:

SELECT
vdto.col4 AS col1
vdfrm.col4 AS col2
FROM t3
LEFT JOIN (SELECT ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2 DESC) AS rnk,a.* FROM t1 a)
vdto ON vdto.col3=t3.col1 AND vdto.rnk=1
LEFT JOIN (SELECT ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2 DESC) AS rnk,a.* FROM t2 a)
vdfrm ON vdfrm.col3=t3.col1 AND vdfrm.rnk=1

What I have tried:

I have also tried the below query, but it is not working:

SELECT 
@row_no := IF(@prev_val = col1, @row_no + 1, 1) AS rnk
, @prev_val := col2 AS col2
FROM t1,
(SELECT @row_number := 0) AS X,
(SELECT @prev_val := 0) Y
ORDER BY col1 ASC , col2 DESC

But I need to make sure that any solution will work in both lower and and higher versions of MySQL.