Nasouh Almrstani Nasouh Almrstani - 5 months ago 12
SQL Question

Mysql get recent rows by date for each specific group

i have Table (afk_map_data):

id map_id state_id measures_id value created_date


the question is >> i want to get only the latest rows for measures_id value by the recent created_date

i have tried many solutions i tried many things but it was stupid, i post this select statement to be my question clear

SELECT md.id,md.measures_id,md.value,md.created_date FROM afk_map_data md
WHERE
md.map_id = 1
AND md.state_id = 2
AND md.measures_id = 4


the result was :

id measures_id value created_date
1 4 777 2016-07-03
2 4 888 2016-07-03
3 4 999 2016-07-02
4 4 555 2016-07-02

the result that i want is :

id measures_id value created_date
1 4 777 2016-07-03
2 4 888 2016-07-03

Thanks a lot ..

Answer

Using MAX Condition on Date Column

 Select TT.id,T.measures_id,TT.value,T.d from afk_map_data TT
        INNER JOIN (
        SELECT  measures_id,MAX(created_date)d FROM afk_map_data
        GROUP BY measures_id )T
        ON  T.d = TT.created_date
WHERE  
 TT.map_id = 1 
 AND TT.state_id = 2
 AND TT.measures_id = 4 
Comments