Converting MSSQL to MYSQL - ROW_NUMBER and WITH CTE

Hello

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
      SELECT CTE.STOCKCODE,CTE.AMOUNT,CTE.MINQTY,CTE.fixed,CTE.SPECIALFLAG,CTE.SPECIALPRICE
            FROM CTE
            WHERE RN = 1;

What I need is to return the row with the Lowest AMOUNT for each MINQTY.
But
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.
p-platerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
In MySQL you can use just a subquery:
      INSERT INTO TABLE2
      SELECT CTE.STOCKCODE,CTE.AMOUNT,CTE.MINQTY,CTE.fixed,CTE.SPECIALFLAG,CTE.SPECIALPRICE
            FROM TABLE1 CTE
            INNER JOIN (SELECT MINQTY, MIN(AMOUNT) AMOUNT FROM TABLE1 WHERE FIXED <> 'Y' GROUP BY MINQTY
                      UNION SELECT MINQTY, AMOUNT AMOUNT FROM TABLE1 WHERE FIXED = 'Y') T
            ON cte.MINQTY = T.MINQTY AND CTE.AMOUNT = T.AMOUNT;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
p-platerAuthor Commented:
Thanks

But when I try to run it I get "Error Code 1137 Can't reopen Table CTE"
0
p-platerAuthor Commented:
Sorted that issue out - I was using a Temporary table which you can't refer to twice!

How can I achieve it with a Temporary table?
(I really need to use Temporary tables as there could be dozens of this query running at the same time)
I Could use a Cursor but would like to avoid that if possible.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

chaauCommented:
Sorry, it is a limitation of MySQL, and AFIAK has not been fixed in any new versions. Can you describe how you use it. Maybe we can help you how to avoid using TEMPORARY tables
0
p-platerAuthor Commented:
What I'm thinking of doing is making a table in the database to store the records with the connection_id as an extra column.
Do the Joins then delete all records for that connection_id from the table.

Does that sound sensible or will that have a large overhead on the server?
0
chaauCommented:
I think it should be fine unless your application is a multithreaded and different threads share the same connection id
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.