A join that should be simple but I'm missing something

I have two tables. Every record in Table1 is unique but Table2 has records where more than one can have the same EquipmentID. (Equipment can have more than one certificate.)

Table1
---------------------------------------------------------------
EquipmentID
Name
Created

Table2
---------------------------------------------------------------
CertificateID
EquipmentID
Created

I’m wanting to pull every record from Table1 and the CertificateID of the most recent record (max CertificateID) pointing to the EquipmentID.

Currently the SQL looks a little like this…

SELECT t1.*, t2.CertificateID FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.EquipmentID = t2.EquipmentID

Thanks!

Have you tried


SELECT t1.*, MAX(t2.CertificateID) FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.EquipmentID = t2.EquipmentID

This is assuming that CertificateID is a numeric/decimal datatype.

I actually tried this earlier but it gives me an error: Column ‘dbo.Table1.EquipmentID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

wolfshade, you’re missing a GROUP BY clause – also, MAX works just fine on strings, too

the requested solution is –

SELECT t1.Name
     , t2.Created
  FROM Table1 AS t1
LEFT OUTER
  JOIN ( SELECT EquipmentID
              , MAX(CertificateID) AS latest
           FROM Table2
         GROUP
             BY EquipmentID ) AS m
    ON m.EquipmentID - t1.EquipmentID
LEFT OUTER
  JOIN Table2 AS t2
    ON t2.EquipmentID = t1.EquipmentID
   AND t2.CertificateID = m.latest  

although i would’ve expected “latest” to be defined by MAX(t2.Created), and not by the highest CertificateID

Works perfect. Thanks!