Get one row from detail/child table

[DL]

Hi,

-- ddl
/*
create table #tmp (col1 int);
insert into #tmp values(1);
insert into #tmp values(2);
insert into #tmp values(3);

create table #tmpChild (col1 int, fkCol int, Num int);
insert into #tmpChild values(1,1,3);
insert into #tmpChild values(2,1,2);
insert into #tmpChild values(3,2,1);
*/

-- get parent and child data (outer join)
select *
from #tmp t Left Outer JOIN #tmpChild tC
ON t.col1 = tC.fkCol

-- resultset
/*
/col1 col1 fkCol Num
----------- ----------- ----------- -----------
1 1 1 3
1 2 1 2
2 3 2 1
3 NULL NULL NULL

(4 row(s) affected)
*/

-- desired resultset
/*
/col1 col1 fkCol Num
----------- ----------- ----------- -----------
1 1 1 3
-- eleminate next row, want only Max(Num) row from the child tbl with
same FK (parentID)
-- 1 2 1 2
2 3 2 1
3 NULL NULL NULL
*/

In other words, desired results is as follows:
/*
/col1 col1 fkCol Num
----------- ----------- ----------- -----------
1 1 1 3
2 3 2 1
3 NULL NULL NULL
*/

How to accomplish this task? ENV: MS SQL Server 2000

TIA

[Anith Sen]

Add the following to the end of your query:

....
AND tC.Num = ( SELECT MAX( t2.Num )
FROM #tmpChild t2
WHERE t2.fkCol = tC.fkCol ) ;

--
Anith