SQL Query - Get Most Recent Revision

Question

Say I have parent table Projects:

ProjectID  ProjectNam
1          Test Project 1
2          Test Project 2

and child table ProjectRevisions:

ProjectRevID ProjectID DateCreated
11           1         10/15/2009
12           1         10/19/2009
13           1         10/25/2009
21           2         10/05/2009

How do I end up with the most recent ProjectRevision for each Project, like this:

ProjectRevID ProjectID DateCreated
13           1         10/25/2009
21           2         10/05/2009

Answer 1

select x.mProjectRevID, p.ProjectID, p.ProjectNam, x.mDateCreated
from Projects p
inner join
(
   select projectID
   , max(ProjectRevID) as mProjectRevID
   , max(DateCreated) as mDateCreated
   from ProjectRevisions
   group by ProjectID
) x
on x.projectID = p.ProjectID

assuming that ProjectRevID and DateCreated are both "going in the same direction" i.e. the next revision receives a higher ID than the previous one.

Joining from Projects allows you to access other columns from Projects, if need be.

Answer 2

The query below will work regardless of any relationship between ProjectRevId and DateCreated.

SELECT *
FROM ProjectRevisions
INNER JOIN (
  SELECT ProjectId
    , MAX(DateCreated) AS DateCreated
  FROM  ProjectRevisions
  GROUP BY ProjectId
  ) AS CurrentRevision
  ON CurrentRevision.ProjectId = ProjectRevisions.ProjectId
  AND CurrentRevision.DateCreated = ProjectRevisions.DateCreated

Answer 3

select ProjectRevID, ProjectID, DateCreated
from Projects p
  inner join ProjectRevisions
  on ProjectRevisions.ProjectId = p.ProjectId
where ProjectRevId = (
  select ProjecRevId
  from ProjectRevisions
  where ProjectId = p.ProjectId
  order by DateCreated desc
  limit 1
  )