Click here to Skip to main content
15,355,050 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:

SQL
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:

SQL
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.
Posted
Updated 16-Feb-22 9:06am
v3

1 solution

In MySQL, RANK() function is supporting since version 8.0. You can achieve this through Row_Number. Please check the following link for same scenario and sample scripts:
MySQL :: Row numbering, ranking: how to use LESS user variables in MySQL queries[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900