Pho Nguyen Pho Nguyen - 5 months ago 7
SQL Question

#1064 - You have an error in your SQL syntax - RANK FUNCTION

SELECT RANK() OVER (ORDER BY `point`) FROM `user`


This is my query, I excute it in phpmyadmin, and receive the message:


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(ORDER BY `point`) FROM `user` LIMIT 0, 30' at line 1"


I try to find the error but just can't.
Please help me!

Answer

MariaDB doesn't support window functions. You can do this using a correlated subquery:

SELECT u.*,
       (SELECT 1 + COUNT(*)
        FROM user u2
        WHERE u2.point < u.point
       ) as rank
FROM `user` u;