MySQL Forums
Forum List  »  PHP

ROW_NUMBER() PARTITION BY doesn't recognise Alias. MYSQL 8.0.21
Posted by: Olamiji Akeem Odeyemi
Date: July 13, 2021 04:36AM

Hello Everyone,
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'
FROM (
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'
FROM table)
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

Options: ReplyQuote


Subject
Written By
Posted
ROW_NUMBER() PARTITION BY doesn't recognise Alias. MYSQL 8.0.21
July 13, 2021 04:36AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.