Wrong syntax for OVER and PARTITION in MySQL syntax

SELECT Id, Price, CustomerId, ROW_NUMBER() OVER (PARTITION BY CustomerId)
FROM Orders;

I get the error “Syntax error: Unexpected ‘(‘ (opening parenthesis)

Does MySQL not support this? I’m pretty sure I’ve done this before and from what I see on google this should work.

mysql -V
mysql  Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using  EditLine wrapper

Answer

Window functions are available in MySQL 8.0 only.

In earlier versions, you can use a subquery – however, your code is missing an ORDER BY clause, which makes the sort inconsistant. Assuming that you want to sort orders of the same customer by their id, you would phrase this as:

SELECT Id, Price, CustomerId, 
    (SELECT COUNT(*) FROM Orders o1 WHERE o1.CustomerId = o.CustomerId and o1.Id <= o.Id) AS rn
FROM Orders o;