Search code examples
mysqlsqldatetimegreatest-n-per-group

Select all newly entered values per unique column in MySQL

drop table if exists aa;
create temporary table aa(`Country` varchar(100),`Month_Year` varchar(100),
                `created_at` timestamp NOT NULL DEFAULT current_timestamp());
insert into aa(Country,Month_Year,created_at) 
    values('Spain1','JUN2020','2020-09-03 14:20:20'), 
            ('Spain2','JUN2020','2020-09-03 14:20:20'),
            ('Spain3','JUN2020','2020-08-03 14:20:20'),
            ('Spain4','JUN2020','2020-07-03 14:20:20'),
            ('Spain5','JUN2020','2020-06-03 14:20:20'),
            ('Spain6','JUL2020','2020-09-01 14:20:20'),
            ('Spain7','JUL2020','2020-09-03 14:20:20'),
            ('Spain8','JUN2020','2020-09-03 14:20:20'),
            ('Spain9','AUG2020','2020-09-01 14:20:20'),
            ('Spain10','AUG2020','2020-05-03 14:20:20'),
            ('Spain11','AUG2020','2020-05-03 14:20:20'),
            ('Porto1','AUG2019','2020-05-03 14:20:20'),
            ('Porto2','AUG2019','2020-05-03 14:20:20'),
            ('Porto2','AUG2019','2020-05-03 13:20:20'),
            ('Porto2','AUG2011','2020-05-03 13:20:20');
select * from aa;

Produces:

enter image description here

How to modify the select query, in order to leave only the highlighted columns? The logic is that per unique value in Month_Year column, only the rows with the newest created_at per unique Month_Year are left.

https://www.db-fiddle.com/f/wEuUpiDyEHq8SZtLDpBm5Q/0#

Solution

  • One option filters with a subquery:

    select t.*
from aa t
where t.created_at = (
    select max(t1.created_at)
    from aa t1
    where t1.month_year = t.month_year
)
order by created_at desc

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f7d29f55ff6f3c573aec0887415b87b5