[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting MSSQL to MYSQL - ROW_NUMBER and WITH CTE

Posted on 2014-08-11
6
Medium Priority
?
523 Views
Last Modified: 2014-08-18
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.
0
Comment
Question by:p-plater
  • 3
  • 3
6 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40254950
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
 

Author Comment

by:p-plater
ID: 40254987
Thanks

But when I try to run it I get "Error Code 1137 Can't reopen Table CTE"
0
 

Author Comment

by:p-plater
ID: 40256962
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 25

Expert Comment

by:chaau
ID: 40257236
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
 

Author Comment

by:p-plater
ID: 40257579
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
 
LVL 25

Expert Comment

by:chaau
ID: 40257603
I think it should be fine unless your application is a multithreaded and different threads share the same connection id
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question