asked on

MAX(Time) in MySQL

I have a Query I am running but it doesn't seem to ge thte max(UploadTime) on every query. I looked in the data base and saw times greater than the time being returned Example:
ID UploadTime Tech tID
3899      11/10/2014 12:40      Tech1        1067      3      2      5/19/2014      10/30/2014      649.25 <-- returns this one
3901      11/10/2014 13:19      Tech2             1067      3      2      5/19/2014      11/10/2014      649.25
3902      11/10/2014 13:25      Tech4             1067      3      2      5/19/2014      11/10/2014      649.25

DROP TABLE IF EXISTS Temp_MyTools;
CREATE TABLE Temp_MyTools(
Select sa.ToolType, sa.SerialNumber, MAX(cd.UploadTime) , cd.DefaultLocationIndex
from SerialA as sa
LEFT JOIN Control as cd ON cd.SerialNumber = sa.SerialNumber
where sa.tooltype in('CO')
AND Dead = 0 
GROUP BY sa.SerialNumber);
Select ToolType, SerialNumber FROM Temp_MyTools WHERE DefaultLocationIndex = 0

Open in new window

UnifiedIS

Does column [Dead] come from table Control?
Do all 3 records have tooltype = 'CO'?

Jim Horn

For starters, your subquery's GROUP BY clause is missing sa.ToolType and cd.cd.DefaultLocationIndex.
All columns not participating in an aggregate such as SUM, COUNT, or MAX have to be in the GROUP BY.

If you'd like some more reading on GROUP BY I have an image and code-heavy tutorial out there called SQL Server GROUP BY Solutions

r3nder

ASKER

Yes all are tooltype are 'CO' and I added sa. to Dead

r3nder

ASKER

@jim sorry it only added everything to the result set - not what I am looking for - I am looking for the max date where defaultlocationIndex = 0 (eventually 1 then 2)

Jim Horn

Should be fixed anyways, as I'm surprised the code executes without throwing an error.

>I am looking for the max date where defaultlocationIndex = 0 (eventually 1 then 2)
Then defaultlocationIndex needs to be in the GROUP BY.

awking00

Your question is a little ambiguous given that your example shows 4 column names (with apparently 9 column values), none of which are tool_type, serial_number, defaultlocationindex, or dead as mentioned in your query. Perhaps you can post some sample data for the SerialA and Control tables along with their structure (i.e. column names and datatypes) and your expected results.

ASKER CERTIFIED SOLUTION

r3nder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

r3nder

ASKER

Thanks anyway