How to get latest record from child table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to get latest record from child table

Author

Topic

gdquinn
Starting Member

4 Posts

Posted - 2007-12-10 : 12:08:39

I am trying to create a SQL statement that for every record from main table, it will read the one of the child record if any. If it is found, I want to get the latest record from the child table with some fields.

For exmaple, I use this code:


select  b.priority, b.lognumber, a.assignedtologonid, a.lastupdatedatetime, a.code
from problem as b 
left outer join 
Problemassignment as a  
on b.lognumber = a.lognumber

I am interseting getting the latest record on a.lastupdatedatetime.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-12-10 : 12:38:58

where a.lastupdatedatetime = (select max(lastupdatedatetime) from Problemassignment where lognumber = b.lognumber)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-10 : 13:10:51

select top 1 b.priority,
b.lognumber,
a.assignedtologonid,
a.lastupdatedatetime,
a.code
from problem as b
left join Problemassignment as a on a.lognumber = b.lognumber
order by a.lastupdatedatetime desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources