row_number() over(partition by col) is not working in Mysql 5.7 version

Question

how to achieve the mysql 8.0 row_number() function in mysql below 8.0 version without using session variable?

SELECT user_name,user_id
FROM   (SELECT user_name,
               user_id,
               pkval,
                 RANK()  OVER(PARTITION BY user_id ORDER BY pkval desc) rn
        FROM   usertable) t
WHERE  rn = 1

equivalent query for MySql below 8.0 version. if i execute this in mysql version 5.7 im getting error as

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY user_id ORDER BY pkval desc) rn
FROM usertable' at line 5 0.047 sec

Upgrade? 5.7 dates back to 2013. 5.7 series release since are bug-fix only, and there's a [TON of important stuff added with 8.0](https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html)... Windowing functions, CTEs, lateral joins, better default null and date handling, performance, and much more. Because of stagnation prior to and for some time following the Oracle acquisition, 5.7 was already so far behind even at the original launch it doesn't even really qualify as a modern database engine. — Joel Coehoorn, Nov 19 '20 at 17:25

score 1 · Accepted Answer · answered Nov 19 '20 at 17:14

1

You can filter on the latest pkval per user_id with a subquery:

select user_name, user_id
from usertable t
where pkval = (
    select max(t1.pkval) from usertable t1 where t1.user_id = t.user_id
)

answered Nov 19 '20 at 17:14

GMB

216,147
25
84
135

1

thank you so much it works. – Vasanth Nov 19 '20 at 17:32

row_number() over(partition by col) is not working in Mysql 5.7 version

1 Answers1