How to find the record in a table that contains the maximum value?

Question

Although this question looks simple, it is kind of tricky.

I have a table with the following columns:

table A:
  int ID
  float value
  datetime date
  varchar(50) group

I would like to obtain the "ID" and "value" of the records that contain the maximum "date" grouped by the column "group". Something like "what is the newest value for each group?"

I can get each group and its maximum date:

SELECT group, MAX(date) FROM A GROUP BY group; -- I also need the "ID" and "value"

But I would like to have the "ID" and value of the record with the highest date.

Making a JOIN between A and the result could be the answer, but there is no way of knowing which record MAX(date) refers to (in case the "date" repeats).

Can someone help?

Answer 1

You could try with a subquery

select group, id, value, date from A where date in
( select MAX(date) as date
  from A
  group by group )
order by group