How to find the record in a table that contains the maximum value?
If date is unique, then you already have your answer. If date is not unique, then you need some other uniqueifier. Absent a natural key, your ID is as good as any. Just put a MAX (or MIN, whichever you prefer) on it:
SELECT *FROM AJOIN ( --Dedupe any non unqiue dates by getting the max id for each group that has the max date SELECT Group, MAX(Id) as Id FROM A JOIN ( --Get max date for each group SELECT group, MAX(date) as Date FROM A GROUP BY group ) as MaxDate ON A.Group = MaxDate.Group AND A.Date = MaxDate.Date GROUP BY Group) as MaxId ON A.Group = MaxId.Group AND A.Id= MaxId.Id