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
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