r3nder
asked on
max date and only 1 record per toolnumber
I am trying to get 1 maxdate from a table foreach toolnumber inserted but I keep getting multiple for each tool number. There should be 1 row returned for toolnumber 1, 1 for toolnumber 2, 1 for toolnumber 3 etc..........
SELECT
a.UploadTime
,a.[ToolNumber]
,a.[ToolSize]
,a.[ToolStyle]
,a.[DefaultLocationIndex]
FROM ToolAssemblies a
INNER JOIN( SELECT MAX(UploadTime) as UploadTime ,[ToolNumb[embed=file 905894]er]
,[ToolSize]
,[ToolStyle]
,[DefaultLocationIndex] FROM ToolAssemblies WHERE ToolNumber IN ( 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 24 , 25 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 39 , 40 , 47 , 51 , 56 , 66 , 71 , 76 , 79 , 82 ) GROUP BY ToolNumber, ToolSize,ToolStyle,DefaultLocationIndex ) b
ON a.UploadTime = b.UploadTime
ORDER BY ToolNumber
Capture.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect Thank you Aneesh
Open in new window