sql - MySQL - Order by values in separate table -

sql - MySQL - Order by values in separate table -

i have 2 mysql tables. table1:

id name otherid

table2:

id otherid time

in each table, "id" unique identifier every row (and primary key) "otherids" correspond between tables. there may several (or 0) rows in table2 have otherid corresponds otherid in table1. table

for example, table 1:

id name otherid 1 bob 24 2 joe 326

table 2:

id otherid time 4 326 2014-03-12 023:38:59 5 24 2013-02-11 22:05:21 6 24 2013-02-10 13:27:58

i looking fetch every row in table1, ordered recent time of rows in table2 go according otherid. in example, result looking for:

id name time 2 joe 2014-03-12 023:38:59 1 bob 2013-02-11 22:05:21

this because joe has recent time table2.

see below illustration :

select table1.*, max(table2.time) table1 inner bring together table2 on table1.otherid = table2.otherid grouping table2.otherid order table2.id

see sqlfiddle demo

mysql sql aggregate-functions greatest-n-per-group