How to get the most recent record using TSQL?

Question

Table a
ID Name
1  aa
2  bb
3  cc

Table b
ID Name  DateTime     aID
1  a1    2010-10-10   1
2  b1    2010-11-10   1
3  c1    2011-01-01   1
4  d1    2010-09-09   2
4  e1    2010-09-09   2


SELECT a.Name, b.Name
FROM Table a
LEFT JOIN Table b ON (a.ID = b.aID, b.status = 1)

In result of this above query, I need only those record where max(datetime) in table b. Result:-

aa, c1

Note: In my real query, i have many many joins and where conditions. Group by won't work here.

Answer 1

This is a method that uses a ranking function to identify the "biggest" date, then filters down the query. Caution: It may not perform very well under certain circumstances.

SELECT a.Name, b.Name
FROM Table a
LEFT JOIN (
  select aID, DateTime, rank() over (order by b.DateTime desc) as rownum
  from TableB
) b ON a.ID = b.aID and b.status = 1
where
b.rownum = 1 -- first row

Answer 2

WHERE b.DateTime = (SELECT MAX(DateTime) FROM b)