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

Question

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

Consider the following table:

CREATE TABLE A (
  id INT,
  value FLOAT,
  "date" DATETIME,
  group VARCHAR(50) 
);

I would like to obtain the ID and value of the records that contain the maximum date grouped by the column group. In other words, something like "what is the newest value for each group?" What query will answer that question?

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).

Sample data:

INSERT INTO A
  VALUES
(1, 1.0, '2000-01-01', 'A'),
(2, 2.0, '2000-01-02', 'A'),
(3, 3.0, '2000-01-01', 'B'),
(4, 2.0, '2000-01-02', 'B'),
(5, 1.0, '2000-01-02', 'B')
;

Mussman

Duplicate of "SQL query for finding representative rows in a table", especially given that neither state how multiple max rows should be handled. Which should be closed as duplicate is a toss-up, given each has answers with unique approaches. Possibly a duplicate of "How do I select only the latest entry in a table for each group when each latest is a single row?". – Kinsley

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

Fortify

Answer 2

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

Grijalva

Answer 3

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

Roundelay

Answer 4

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

Krefeld

That makes it kind of hard to find a single most recent record for each group :] – Krefeld