How to write a query to retrieve latest record only in one to many relationship?

Question

I have two tables: tableA and tableB. One row in tableA has many records in tableB.

How do I write a select query to join both tables and return only the latest record from tableB when joining.

Ex: TableA

ID   NAME
--
ABC  JOHN

TableB

ID   Role LastUpdateDate
--
ABC  M    07/07/15
ABC  C    03/04/14
ABC  S    03/04/17

I want to retrieve ABC, JOHN, S, 03/04/17

Answer 1

you can try something like this.

select tableB.ID,tableB.Role, Max(tableB.LastUpdateDate)
from tableA inner join tableB
on tableA.ID = tableB.ID
group by tableB.ID,tableB.Role;