joining table on on last entered record

Dear All,

What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.

I'd like to select every record in table A but only joining the last relevant record from table B. So:

Table A:

A1 Prj1
A2 Prj2

Table B:

B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007

So I'd like to list using the most efficient way this:

A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007

I'm assuming this is NOT the most efficient way:

select A, (select top 1 date from B orderBy ...)

Any suggestions?Maybe this:

select A, B
from A
inner join A on A.Aid = B.Aid
where B.date = (select max(B.date) from B where...) ...

this works faster but is there a better way? (I'm sure there is)

Anyone?|||I use this:
select A,
B
from A
inner join --LastRecords
select A,
max(date) as date
from A
group by A) LastRecords
on A.A = LastRecords.A
and A.date = LastRecords.date
...but I can't promise that it is faster.|||Another possibility is:
SELECT A.aid
,A.prj
,MAX(B.DATE)
FROM A
INNER JOIN B ON B.aid = A.aid
GROUP BY A.aid, A.prj
Don't know how this will perform but as long as you have the right index (I'd recomment one on "B.Aid, B.date") I don't think it will differ much between the various methods.|||Thanks for that, how about when the record in table A have no records in table B yet, but I'd still like to list it, but with a NULL value in the columns from table B?|||use a LEFT OUTER JOIN instead of INNER JOIN|||Thanks for that