Search code examples
mysqlsqlsubquerygreatest-n-per-groupmysql-5.7

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

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

Solution

  • 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
)