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

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. ;)