0

I recently updated my mysql instance on ubuntu to 8.0.26 from 5.7 to work with windowed functions. I checked the version with select @@version and it shows 8.0.26. But my queries to get data over partition by are still not working.

The table structure looks like this

Table sensor_data
id   asset_code   server_time

Below query gives Error Code: 1064. You have an error in your SQL syntax;

SELECT id, ROW_NUMBER() OVER (PARTITION BY asset_code ORDER BY server_time DESC) rank
FROM sensor_data
dev Joshi
  • 305
  • 2
  • 21

1 Answers1

2

The problem is the rank alias you gave to the call to ROW_NUMBER. In MySQL 8+, RANK becomes a reserved keyword referring to the analytic function RANK(). Note that using an alias of rank on MySQL 5.7 and earlier was perfectly fine. Just change the alias and your code should work:

SELECT id, ROW_NUMBER() OVER (PARTITION BY asset_code ORDER BY server_time DESC) rnk
FROM sensor_data
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360