select a.*,b.ADate,b.ACount from table1 a,table2 b
where a.aid=b.aid
and b.ADate=(select max(ADate) from table2 where aid=b.aid)
where a.aid=b.aid
and b.ADate=(select max(ADate) from table2 where aid=b.aid)
from table1 t1
inner join (select * from table2 where aid=(select aid from table2 group by aid having adate=max(adate))) t2 on t1.aid=t2.aid
from table1 a
join talbe2 b on a.Aid=b.Aid
join (
select aid,adate=max(adate) from table2 group by aid
) b1 on a.aid=b1.aid and b.adate=b1.adate
from table1 a join table2 b on a.Aid = b.Aid and
b.Adate = (select max(Adate) from table2 where b.Aid = Aid)
from table1 a
join talbe2 b on a.Aid=b.Aid
join (
select top 1 aid,adate=max(adate) from table2 group by aid
) b1 on a.aid=b1.aid and b.adate=b1.adate