Click here to Skip to main content
15,920,896 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[^]
 
Share this answer
 

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