Blake Rivell Blake Rivell - 5 months ago 7
SQL Question

Selecting the Id of the item with a MAX value when doing a left join

Each product in the database can have many revisions. Some products might not have any revisions at all.

ProductRevision table has the following fields: Id, Version, SubmitDate

I am trying to figure out how I can select a field called LatestRevisionId based on the MAX SubmitDate and if not revision then the field will be null

SELECT p.Id, p.Name, p.Price
FROM Product p
LEFT OUTER JOIN ProductRevision pr ON p.Id = pr.ProductId


Do I have to do a sub select in my select? I really want to try and use HAVING but can't figure out how to do it with a left join.

I was trying to do the following as a sub select:

(SELECT Id
FROM ProductRevision
WHERE ProductId=p.Id
HAVING SubmitDate=MAX(SubmitDate)
) AS LatestVersionId


Please note that I am using SQL SERVER 2008

Answer

Here's one option using row_number:

SELECT *
FROM (
    SELECT p.Id, p.Name, p.Price, pr.id as LatestRevisionId, 
           row_number() over (partition by p.Id order by pr.SubmitDate desc) rn
    FROM Product p
    LEFT OUTER JOIN ProductRevision pr PN p.Id = pr.ProductId
) t
WHERE rn = 1

This will select a single Product with the latest matching row from the ProductRevision table.


If you just prefer to use max, then you need to join the table back to itself again:

SELECT p.Id, p.Name, p.Price, pr.id as LatestRevisionId
FROM Product p
LEFT OUTER JOIN ProductRevision pr PN p.Id = pr.ProductId
LEFT OUTER JOIN (SELECT ProductId, MAX(SubmitDate) MaxSubmitDate
                 FROM ProductRevision 
                 GROUP BY ProductId) mpr ON pr.ProductId = mpr.ProductId AND
                                           pr.SubmitDate = mpr.MaxSubmitDate

This could perhaps return duplicates though if multiple revisions share the same date.

Comments