create table #main ( id int NOT NULL, data nvarchar(50) )
GO
create table #tran ( id int NOT NULL, main_id int NOT NULL, created_on datetime, extra_data nvarchar( 50 ) )
GO
insert into #main values(1, 'hello')
insert into #main values(2, 'world')
insert into #main values(3, '!!!')
insert into #tran values(1, 1, '2006-01-01', 'foo')
insert into #tran values(2, 1, '2006-01-02', 'bar')
insert into #tran values(3, 2, '2005-01-01', 'baz')
insert into #tran values(4, 2, '2005-01-02', 'a')
select * from #main
left join (SELECT main_id, max(created_on) AS co FROM #tran GROUP BY main_id) AS _tran ON (_tran.main_id = #main.id)