Converting MSSQL to MYSQL - ROW_NUMBER and WITH CTE
Posted on 2014-08-11
I have the following MS SQL Query and need to convert it to MYSQL.
WITH CTE AS
(SELECT TABLE1.*,ROW_NUMBER() OVER(PARTITION BY MINQTY ORDER BY FIXED DESC,AMOUNT) RN FROM TABLE1)
INSERT INTO TABLE2
WHERE RN = 1;
What I need is to return the row with the Lowest AMOUNT for each MINQTY.
If the FIXED Field is 'Y' then I need to return that Row (For each MINQTY - If there are two MINQTY of 10 and one FIXED field is 'Y' then I need that row even if the price is lower on the Row that is not FIXED)
Note: the FIXED parameter is with the MINQTY - If there is a Not FIXED record with a Min of 5 and a Min record that is FIXED with a Min of 8 then I need both rows.