ROW_NUMBER() PARTITION BY doesn't recognise Alias. MYSQL 8.0.21
Please kindly help me check what I have wrong in this my query?
SELECT col1, col2, col3 AS 'QUANTITY', col4 AS 'VALUE', YEAR(reg_date) AS 'YEAR'
SELECT ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY QUANTITY DESC) AS Rank, col1, col2, MAX(SUM(QUANTITY)) AS 'QUANTITY', SUM(VALUE) AS 'FOB_VALUE',
YEAR(reg_date) AS 'YEAR'
WHERE YEAR(reg_date)='2015' AND Rank <= 10;
*****Error is below :
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 'Rank, col1, col2, MAX(SUM(QUANTITY)), SUM(VALUE) at line 3
It doesn't recognised the Rank Alias of the ROW_NUMBER() PARTITION BY . Please, what am i doing wrong?
MySQL version i use is 8.0.21