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

Open in new window

Capture.PNG

dsacker

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

ASKER CERTIFIED SOLUTION

Aneesh

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

Perfect Thank you Aneesh