Link to home
Create AccountLog in
Avatar of r3nder
r3nderFlag for United States of America

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
Avatar of dsacker
dsacker
Flag of United States of America image

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
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of r3nder

ASKER

Perfect Thank you Aneesh