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

0 votes
asked Dec 17, 2008 by loofer

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?

5 Answers

0 votes
answered Dec 17, 2008 by tony-andrews
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

0 votes
answered Dec 17, 2008 by stanislas-biron

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

0 votes
answered Dec 17, 2008 by ken-yao
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
0 votes
answered Dec 17, 2008 by redfilter

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
0 votes
answered Dec 17, 2008 by tom-h

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

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...