join two tables and only return the latest data for the child table

I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:

select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?

I tried to create a function to get the latest sentence for a chargeID like the following:

select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?

thanks,

if you are on 2005, you can use row_number(), as follows:

select * from(
select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
) t
where rn=1
|||

Your suggestion works, thanks.

But when I modified the query to:

select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

where rn=1

the query fails with the error "Invalid column name 'rn'.", why?

|||Because Where applies to the source fields, not the result fields, so your alias "as rn" hasn't been applied yet.

That's why Akuz nested it inside another Select. The outer Select/Where can use the alias.