CODEWITHSUNDEEP
sqldatetimegroup-bymaxgroupwise-maximum
Jonas
Jonas

Reputation: 1373

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

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

Upvotes: 11

Views: 24138

Answers (4)

Mark Brackett
Mark Brackett

Reputation: 85655

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

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52346

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

Upvotes: 4

Andrew Kennan
Andrew Kennan

Reputation: 14157

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

Upvotes: 0

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340241

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

Upvotes: 8

Related Questions