CODEWITHSUNDEEP
mysqlsqljoingreatest-n-per-group
Carlvic Japitana Lim
Carlvic Japitana Lim

Reputation: 149

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

I have 2 tables

First is

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 is

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 this query

SELECT monitor.label, addresses.datetime FROM monitor INNER JOIN addresses ON monitor.ad=addresses.ad ORDER BY 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 my desire output is to get the recent row and 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 help me on this matter?

NOTE* addresses AD is not primary or unique but monitor AD is Primary

Upvotes: 2

Views: 208

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

This is called mysql group-wise maximum and there are many ways of doing it and one way is

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

Upvotes: 2

Amit
Amit

Reputation: 46361

Use a GROUP BY Clause.

SELECT label, MAX(datetime)
FROM monitor INNER JOIN addresses
ON monitor.ad = addresses.ad
GROUP BY label

Upvotes: 0

Jens
Jens

Reputation: 69515

Simply use group by and max:

SELECT monitor.label, max(addresses.datetime) 
FROM monitor INNER JOIN addresses ON monitor.ad=addresses.ad 
group by monitor.label 
ORDER BY monitor.label;

Upvotes: 0

Related Questions