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
Post a Comment