[FIXED] MySQL throwing error 1064, doesn't seem to recognize OVER command

Issue

My query is:

SELECT *,
ROW_NUMBER() OVER (ORDER BY score ASC)
FROM submissions

The error message I receive is:

#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 '(ORDER BY score ASC) FROM submissions LIMIT 0, 25' at line 2

I am running this query in phpMyAdmin. I notice that OVER is not colored blue, nor does is it suggested as I type, unlike other command words (ORDER, ASC, etc).

This simpler query runs just fine:

SELECT * FROM submissions

I've tried putting things in quotes, using the RANK function instead, and fiddling with whitespace, but the query still doesn't run. What is wrong here?

Solution

My guess is that you are running a version of MySQL which is earlier than 8+, one which does not support ROW_NUMBER. There are a few options for simulating ROW_NUMBER in earlier versions of MySQL. One is to use user variables:

SELECT *,
    (@row_number:=@row_number + 1) AS rn
FROM submissions, (SELECT @row_number := 0) tmp
ORDER BY score;

Answered By - Tim Biegeleisen