need latest records

[Member 3879881]

Hi i have 2 tables.., one is master another one is child..., ex: Master=>Pid is primary key =========== Pid Name 1 m1 2 m2 3 m3 4 m4 5 m5 6 m6 Child=>Cid is primary key ========== Cid Pid Name 1 2 nm1 2 2 nm2 3 1 nm3 4 5 nm4 5 6 nm5 Now i need the lastest records..., O/p: ====== Pid Cid Name 6 5 nm5 5 4 nm4 4 null m4 3 null m3 2 2 nm2 //in child table have 2 records for pid...,in tht latest is Cid=2 1 3 nm3

Thanks & Regards, Member 3879881, please don't forget to vote on the post

[Niladri_Biswas]

Try this declare @tblMaster table(pid int primary key,name varchar(20)) insert into @tblMaster select 1,'m1' union all select 2,'m2' union all select 3,'m3' union all select 4,'m4' union all select 5,'m5' union all select 6,'m6' declare @tblChild table(cid int primary key,pid int,name varchar(20)) insert into @tblChild select 1,2,'nm1' union all select 2,2,'nm2' union all select 3,1,'nm3' union all select 4,5,'nm4' union all select 5,6,'nm5'

select pid,cid,name from
(
select
m.pid
,c.cid
,case when c.name IS null then m.name
else c.name
end name
,row_number() over(partition by m.pid order by c.name desc) as rn

from @tblChild c
full join @tblMaster m
on c.pid = m.pid
)x(pid,cid,name,rn)
where rn = 1
order by pid desc

:)

Niladri Biswas

[Member 3879881]

whts tht row_number function?

Thanks & Regards, Member 3879881, please don't forget to vote on the post

[Niladri_Biswas]

It's a ranking function. It will give the row numbers. It is available from SQL SERVER 2005 onwards For more info check this article http://www.databasejournal.com/features/mssql/article.php/3577481/RowNumber-function-in-SQL-Server-2005--Part-II.htm[^] I my case I am using this function just to ensure the latest records. :)

Niladri Biswas

[Ashfield]

This should do it

select m.pid,c.cid,c.name
from Master m
join Child c on c.pid = m.pid
and c.cid = (select max(cid) from Child c1 where c1.pid = m.pid)

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP