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')
;

Duplicate of "[SQL query for finding representative rows in a table](//stackoverflow.com/q/375262/90527)", 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?](//stackoverflow.com/q/374846/90527)". — outis, May 10 '22 at 23:38

score 8 · Answer 1 · answered Dec 18 '08 at 00:25

8

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

answered Dec 18 '08 at 00:25

Vinko Vrsalovic

330,807
53
334
373

score 4 · Answer 2 · answered Dec 18 '08 at 01:24

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

score 2 · Answer 3 · answered Dec 18 '08 at 03:17

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

score 0 · Answer 4 · answered Dec 18 '08 at 00:26

0

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

answered Dec 18 '08 at 00:26

Andrew Kennan

13,947
3
24
33

That makes it kind of hard to find a single most recent record for each group :] – Andrew Kennan Dec 18 '08 at 00:34

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

4 Answers4

Linked

Related