询问：
SELECT
p.*, d.maxdate
FROM persons p
left join
( SELECT id, MAX(date) MaxDate
FROM device
GROUP BY id
) d ON p.deviceid = d.id
ORDER BY d.maxdate DESC;
| PERSONID | NAME | deviceid | MAXDATE |
-----------------------------------------------------------------
| 1 | John | 11111 | February, 03 2013 02:00:00+0000 |
| 2 | Eric | 22222 | February, 03 2013 00:00:00+0000 |
| 3 | Steve | 33333 | (null) |
我看到您也需要isRegistered专栏，这里是：
询问：
SELECT
p.*, d.maxdate, case when d.maxdate is null then 0 else 1 end as isRegistered
FROM persons p
left join
( SELECT id, MAX(date) MaxDate
FROM device
GROUP BY id
) d ON p.deviceid = d.id
ORDER BY d.maxdate DESC
;
结果：
| PERSONID | NAME | deviceid | MAXDATE | ISREGISTERED |
--------------------------------------------------------------------------------
| 1 | John | 11111 | February, 03 2013 02:00:00+0000 | 1 |
| 2 | Eric | 22222 | February, 03 2013 00:00:00+0000 | 1 |
| 3 | Steve | 33333 | (null) | 0 |
602392714
清零编程群