How to find the record in a table that contains th

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

As long as the Date column is unique for each group I think something like this might work:

SELECT A.ID, A.Value
FROM A
  INNER JOIN (SELECT Group, MAX(Date) As MaxDate FROM A GROUP BY Group) B
    ON A.Group = B.Group AND A.Date = B.MaxDate

Answer 2

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

Answer 3

This is just what analytic functions were made for:

select group,
       id,
       value
from   (
       select group,
              id,
              value,
              date,
              max(date) over (partition by group) max_date_by_group
       from A
       )
where  date = max_date_by_group

Answer 4

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 A
JOIN (
    --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