Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL Max of 2 queries in a Union

Posted on 2014-09-04
3
Medium Priority
?
641 Views
Last Modified: 2014-09-04
I have a MySQL query with 2 statements in a union - I want to get the MAX of both queries off of MaxOfStartTime giving 1 entry for each tool instead of for both. So if query 1 has the maxtime it shows query 1's result and if query 2 has the maxtime it shows query 2's result

(
SELECT SerializedAssets.SerialNumber, 
Max(JobDownholeEquipment.JobID) AS LastOfJobID, 
Max(JobDownholeEquipment.StartTime) AS MaxOfStartTime, 
'Job' AS JobType, Job.Company,Job.Rig,Job.RigNumber,Job.WellName,Job.City,Job.State
FROM SerializedAssets 
INNER JOIN JobDownholeEquipment ON SerializedAssets.SerialNumber = JobDownholeEquipment.ToolNumber
INNER JOIN Job ON Job.JobID = JobDownholeEquipment.JobID
GROUP BY SerializedAssets.SerialNumber
)
UNION
(

SELECT ToolAssemblies.ToolNumber, MAX(ToolAssemblies.Location) AS LastOfLocation, Max(ToolAssemblies.UploadTime) AS MaxOfUploadTime, 'Assembly' AS JobType,
"" AS Company,"" AS Rig,"" AS RigNumber,"" AS WellName,"" AS Cist,"" AS State
FROM ToolAssemblies
GROUP BY ToolAssemblies.ToolNumber
)

Open in new window

0
Comment
Question by:r3nder
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40303737
I think I would create a view from your query, then apply the syntax suggestion for MySQL from this article I wrote: http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
hope this helps
0
 
LVL 6

Author Comment

by:r3nder
ID: 40303767
ok how - I don't really have time to learn right now - I just need to figure  it out. In this particular scenario I have 1 record from each select statement that are the same tool number.
so the data looks like this
---------------------------------
1  | 12/12/2014  | Dallas
-----------------------------------
1  | 01/02/2009  | On job

and so on......
I need the max date of those records so that is displayed
-----------------------------------
1  | 12/12/2014  | Dallas
-----------------------------------
2  | 12/24/2014  | On Job

And so on
0
 
LVL 6

Author Comment

by:r3nder
ID: 40303930
Fixed
 query += " DROP VIEW if exists v; ";
                query += " Create View v as ( ";
                query += " SELECT SerializedAssets.SerialNumber AS ToolNumber,  ";
                query += " Max(JobDownholeEquipment.JobID) AS LastOfLocation,  ";
                query += " Max(JobDownholeEquipment.StartTime) AS MaxOfUploadTime,  ";
                query += " 'Job' AS JobType, Job.Company,Job.Rig,Job.RigNumber,Job.WellName,Job.City,Job.State ";
                query += " FROM SerializedAssets  ";
                query += " INNER JOIN JobDownholeEquipment ON SerializedAssets.SerialNumber = JobDownholeEquipment.ToolNumber ";
                query += " INNER JOIN Job ON Job.JobID = JobDownholeEquipment.JobID ";
                query += " GROUP BY SerializedAssets.SerialNumber ";
                query += " ) ";
                query += " UNION ALL ";
                query += " ( ";
                query += " SELECT ToolAssemblies.ToolNumber AS ToolNumber, MAX(ToolAssemblies.Location) AS LastOfLocation, Max(ToolAssemblies.UploadTime) AS MaxOfUploadTime, 'Assembly' AS JobType, ";
                query += " '' AS Company,'' AS Rig,'' AS RigNumber,'' AS WellName,'' AS City,'' AS State ";
                query += " FROM ToolAssemblies ";
                query += " GROUP BY ToolAssemblies.ToolNumber, 'Assembly' ";
                query += " ) ";
                query += " Order by ToolNumber; ";
                query += " Select * From v Group by ToolNumber ";

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

926 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