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 2005 Forums

Transact-SQL (2005)

How to get latest record from child table

Author

Topic

gdquinn
Starting Member

4 Posts

Posted - 2007-12-07 : 15:08:42

I am going to consider this thread close since I posted it in the wrong forum. Sorry.

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.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-07 : 15:15:35

[code]
select b.priority, b.lognumber, a.assignedtologonid, MAX(a.lastupdatedatetime), a.code
from problem as b
left outer join
Problemassignment as a
on b.lognumber = a.lognumber
group by
b.priority, b.lognumber, a.assignedtologonid, a.code
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 18:24:22

[code]select t.priority,
t.lognumber,
t.assignedtologonid,
t.lastupdatedatetime,
t.code
from (
select b.priority,
b.lognumber,
a.assignedtologonid,
a.lastupdatedatetime,
a.code,
ROW_NUMBER() over (partition by b.lognumber order by a.lastupdatedatetime desc) AS recid
from problem as b
left join Problemassignment as a on a.lognumber = b.lognumber
) AS t
where t.recid = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

gdquinn
Starting Member

4 Posts

Posted - 2007-12-10 : 11:54:28

gdquinn
Starting Member

4 Posts

Posted - 2007-12-10 : 12:09:01

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-10 : 12:09:47

try this:-
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 13:11:50

Cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93974

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources