MySQL error: What is wrong with my syntax for ranking?

Question

My MySQL version is 5.6.
I have a MySQL query that I getting error and need help to understand what I am doin wrong.

Here is the query:

select images.thumbnail16x9_file,
       images.square_file,
       RANK() OVER (ORDER BY images.timestamp desc) rank
from images 
where images.media_id = 875021;

This is the error message:

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 images.timestamp ) rank
from images where images.media' at line 3

I get result back if I do:

select images.thumbnail16x9_file,
       images.square_file
from images 
where images.media_id = 875021 ;

Any insight?

Answer 1

Seeing as you mentioned running MySQL 5.6, I compared the documentation for 5.6 with that of 8.0 and noticed that the RANK() function might not be available in MySQL 5.6.

The documentation for MySQL 5.6 exists here, and for 8.0 exists here.

Searching for the RANK() function in 8.0 returns a section called Window Function Descriptions that seems to cover the RANK() function, while performing the same search in 5.6 doesn't seem to return anything about the function. I'm not 100% sure about this, but would definitely try to find a reference to the RANK() function in the 5.6 documentation for clarification.