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

Open in new window

Capture.PNG
LVL 6
r3nderAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT
a.UploadTime
      ,a.[ToolNumber]
      ,a.[ToolSize]
      ,a.[ToolStyle]
      ,a.[DefaultLocationIndex]  
FROM ToolAssemblies a
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 )
AND a.UploadTime = (SELECT  MAX(UploadTime) FROM ToolAssemblies a WHERE a.[ToolNumber] = b.toolNumber )
ORDER BY ToolNumber
0
 
dsackerContract ERP Admin/ConsultantCommented:
Do you even need the additional INNER JOIN on the same table?
SELECT 
       a.[ToolNumber]
      ,a.[ToolSize]
      ,a.[ToolStyle]
      ,a.[DefaultLocationIndex]
      , MAX(UploadTime) AS UploadTime
FROM ToolAssemblies a
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 a.[ToolNumber]
      ,a.[ToolSize]
      ,a.[ToolStyle]
      ,a.[DefaultLocationIndex]
ORDER BY ToolNumber

Open in new window

0
 
r3nderAuthor Commented:
Perfect Thank you Aneesh
0
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.

All Courses

From novice to tech pro — start learning today.