sql - How do I join the most recent row in one table to another table MYSQL -


i have 2 tables

first

addresses
id                   ad                               datetime
1          121.252.212.121        2015-04-20 08:23:04
2          121.252.212.122       2015-05-20 09:11:06
3          121.252.212.121       2015-05-20 11:56:04
4          121.252.212.123      2015-05-20 09:54:25
5         121.252.212.123      2015-05-20 10:23:04
6        121.252.212.122      2015-05-20 09:50:10
7       121.252.212.123       2015-05-20 12:50:02

second

monitor
server_id                 ad                           label
1                  121.252.212.121                north
2                 121.252.212.122                south
3                121.252.212.123                 east

i use query

select monitor.label, addresses.datetime monitor inner join addresses on monitor.ad=addresses.ad order monitor.label;

myoutput

label                   datetime
north             2015-04-20 08:23:04
north             2015-05-20 11:56:04
south            2015-05-20 09:11:06
south            2015-05-20 09:50:10
east              2015-05-20 09:54:25
east              2015-05-20 10:23:04
east              2015-05-20 12:50:02

but desire output recent row , merge duplicate label example below

mydesire
label                   datetime
north              2015-05-20 11:56:04
south             2015-05-20 09:50:10
east               2015-05-20 12:50:02

anyone can me on matter?

note* addresses ad not primary or unique monitor ad primary

this called mysql group-wise maximum , there many ways of doing , 1 way is

select  m.label,  a.datetime monitor m  join (   select ad, max(datetime) datetime addresses group ad )a on a.ad = m.ad; 

Comments