Michay Carpa Michay Carpa - 3 months ago 6
MySQL Question

Retrieve row for each group having the maximum date

I have this MySQL table:

ID Number Date
1 4 2015-05-30
2 4 2016-03-15
3 5 2016-04-01
4 5 2016-05-01


I want to get unique Number but only get those with the highest date. The result must get ONLY:

ID Number Date
2 4 2016-03-15
4 5 2016-05-01


I tried:

SELECT
*
FROM emp_events ee
Date = (SELECT MAX(Date) FROM emp_events ee1 WHERE ee1.Number = ee.Number));


but not getting all the desired results.

Answer

One way you can accomplish through INNER JOIN

SELECT 
E.*
FROM emp_events E
(
    SELECT 
        Number,
        MAX(Date) max_date
    FROM emp_events ee 
    GROUP BY Number
) AS t
ON E.Number = t.Number AND E.Date = t.max_date

Another way could be using IN:

SELECT 
E.*
FROM emp_events E
WHERE (E.Number,E.Date) IN 
(
    SELECT 
        Number,
        MAX(Date) max_date
    FROM emp_events ee 
    GROUP BY Number
)
Comments