How do I select only the latest entry in a table?

Question

I have a 3 table SQLServer Database.

Project
ProjectID
ProjectName

Thing
ThingID
ThingName

ProjectThingLink
ProjectID
ThingID
CreatedDate

When a Thing is ascribed to a Project an entry is put in the ProjectThingLink table. Things can move between Projects. The CreatedDate is used to know which Project a Thing was last moved too.

I am trying to create a list of all Projects with which Things are currently linked to them, but my brain is failing.

Is there an easy way of doing this?

Answer 1

select p.projectName, t.ThingName
from projects p
join projectThingLink l on l.projectId = p.projectId
join thing t on t.thingId = l.thingId
where l.createdDate =
( select max(l2.createdDate)
  from projectThingLink l2
  where l2.thingId = l.thingId
);

NOTE: Corrected after comment

Answer 2

You can simply do

SELECT Project.projectId, Project.ProjectName, ThingName
FROM Project  INNER JOIN ProjectThingLink
 ON Project.ProjectID = ProjectThingLink.ProjectID INNER JOIN
                     Thing ON ProjectThingLink.ThingID = Thing.ThingID

Will will get a list of every project with every thing linked to it like this:

1 | Project1 | SomeThing

1 | Project1 | ThisThing

2 | Project2 | ThatThing

Answer 3

select * from project inner join (select projectid,max(createdate) as maxdate from projectThingLink group by projectid) as a on projectid = a.projectid and createDate = a.maxDate

Answer 4

Try this:

select p.ProjectID, p.Name, t.ThingID, t.ThingName, l.CreatedDate 
from Project p
inner join (
    select ProjectID, max(CreatedDate) as CreatedDate
    from ProjectThingLink
    group by ProjectID
) lm on p.ProjectID = l.ProjectID
inner join ProjectThingLink l on lm.ProjectID = l.ProjectID and lm.CreatedDate = l.CreatedDate
inner joing Thing t on l.ThingID = t.ThingID

Answer 5

This will almost always give you better performance than the subquery method. You're basically looking for the row which doesn't have any other rows past it rather than looking for the row with the greatest date:

SELECT
     P.ProjectID,
     P.ProjectName,
     T.ThingID,
     T.ThingName
FROM
     dbo.Projects P
INNER JOIN dbo.ProjectThingLinks PTL1 ON
     PTL1.ProjectID = P.ProjectID
LEFT OUTER JOIN dbo.ProjectThingLinks PTL2 ON
     PTL2.ProjectID = ThingID = PTL1.ThingID AND
     PTL2.CreatedDate > PTL1.CreatedDate
INNER JOIN dbo.Things T ON
     T.ThingID = PTL1.ThingID
WHERE
     PTL2.ThingID IS NULL

Once you decide on your business rules for handling two rows that have identical CreatedDate values you may need to tweak the query.

Also, as a side note, a table called "Things" is typically a good sign of a problem with your database design. Tables should represent distinct real life entities. That kind of generality will usually result in problems in the future. If these are resources then they will probably share certain attributes beyond just a name. Maybe your case is a very special case, but most likely not. ;)