how to use SQL group to filter rows with maximum date value

Question

I have the following table

CREATE TABLE Test
    (`Id` int, `value` varchar(20), `adate` varchar(20))
;

INSERT INTO Test
    (`Id`, `value`, `adate`)
VALUES
    (1, 100, '2014-01-01'),
    (1, 200, '2014-01-02'),
    (1, 300, '2014-01-03'),
    (2, 200, '2014-01-01'),
    (2, 400, '2014-01-02'),
    (2, 30 , '2014-01-04'),
    (3, 800, '2014-01-01'),
    (3, 300, '2014-01-02'),
    (3, 60 , '2014-01-04')
;

I want to achieve the result which selects only Id having max value of date. ie

Id ,value ,adate

 1, 300,'2014-01-03'     
 2, 30 ,'2014-01-04'     
 3, 60 ,'2014-01-04'

how can I achieve this using group by? I have done as follows but it is not working.

Select Id,value,adate
from Test
group by Id,value,adate
having adate = MAX(adate)

Can someone help with the query?

score 17 · Answer 1 · answered Jun 10 '14 at 11:46

17

Select the maximum dates for each id.

select id, max(adate) max_date
from test
group by id

Join on that to get the rest of the columns.

select t1.*
from test t1
inner join (select id, max(adate) max_date
            from test
            group by id) t2
on t1.id = t2.id and t1.adate = t2.max_date;

answered Jun 10 '14 at 11:46

Mike Sherrill 'Cat Recall'

91,602
17
122
185

What if `adate` must be determined through 2 other columns that must be handled as union? – fabpico Sep 01 '17 at 11:31
1

@FabianPicone: The principle is the same. Write a query that returns one row per "id", and join on it. You might have to write the UNION query, and then SELECT from it. (SELECT * FROM (SELECT...UNION SELECT...)) – Mike Sherrill 'Cat Recall' Sep 01 '17 at 12:43

score 11 · Answer 2 · answered Jun 10 '14 at 11:46

11

Please try:

select 
  * 
from 
  tbl a
where 
  a.adate=(select MAX(adate) from tbl b where b.Id=a.Id)

answered Jun 10 '14 at 11:46

TechDo

18,398
3
51
64

2

I wonder why this is not considered the best solution. – Matteo Castagna Aug 07 '20 at 17:22
Totally agree, this has been by far the easiest solution to understand and implement. – Andreas L. Feb 22 '22 at 09:41

score 6 · Accepted Answer · answered Jun 10 '14 at 11:49

If you are using a DBMS that has analytical functions you can use ROW_NUMBER:

SELECT  Id, Value, ADate
FROM    (   SELECT  ID,
                    Value,
                    ADate,
                    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Adate DESC) AS RowNum
            FROM    Test
        ) AS T
WHERE   RowNum = 1;

Otherwise you will need to use a join to the aggregated max date by Id to filter the results from Test to only those where the date matches the maximum date for that Id

SELECT  Test.Id, Test.Value, Test.ADate
FROM    Test
        INNER JOIN
        (   SELECT  ID, MAX(ADate) AS ADate
            FROM    Test
            GROUP BY ID
        ) AS MaxT
            ON MaxT.ID = Test.ID
            AND MaxT.ADate = Test.ADate;

score -1 · Answer 4 · answered Jun 10 '14 at 11:46

-1

I would try something like this

Select  t1.Id, t1.value, t1.adate
from    Test as t1
where   t1.adate = (select max(t2.adate) 
                    from Test as t2
                    where t2.id = t1.id)

answered Jun 10 '14 at 11:46

how to use SQL group to filter rows with maximum date value

4 Answers4

Linked