SQL Query - get the latest version

Question

I am using T-Sql with SQL Server 2008. Let's say I have a 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 05/10/2009

How do I achieve the latest ProjectRevision for each project? For example:

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

Answer 1

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 2

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

, ProjectRevID 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
  )