Issue with select subquery in Mysql8.0

Question

Why is below an invalid sql statement in mysql. It works perfectly in oracle.

SELECT originalAmount,fees,id FROM 
(SELECT originalAmount,fees,id, ROW_NUMBER() OVER (PARTITION BY transaction_number ORDER BY eventdate ASC) RANK FROM kir_records where customerid= 1704) 
WHERE RANK = 1;

I immediately get a syntax error as soon as paste this in mysql workbench.

Error: Select is invalid at this position. Expecting '(' at first select.

Is there a workaround to make this work ?

Answer 1

try using this query.

SELECT originalAmount,fees,id FROM 
((SELECT originalAmount,fees,id, ROW_NUMBER() OVER (PARTITION BY transaction_number ORDER BY eventdate ASC) RANK FROM kir_records where customerid= 1704)) 
WHERE RANK = 1;

Answer 2

Look like RANK is a reserved word in MySql. Used backquotes (``) around RANK and it worked as expected. One other thing to take care about is that every derived table (AKA sub-query) must indeed have an alias. Dervied Table alias

Here is the query which worked for me :

SELECT originalAmount,fees,id FROM 
(SELECT originalAmount,fees,id, ROW_NUMBER() OVER (PARTITION BY transaction_number ORDER BY eventdate ASC) `RANK` FROM kir_records where customerid= 1704) AS SomeAlias
WHERE `RANK` = 1;