MySQL 选择某一列具有共同值但另一列具有最大值的行

Question

这里是my table in SQL Fiddle 。我想为每个children_id 找到1 行，其中最大in_time 存在。所以预期的输出是:

----+------------+
id  |  in_time   |
----+------------+
16  | 1518909618 |
18  | 1518913186 |
17  | 1518909862 |
----+------------+

这是我的查询，但没有提供预期的数据:

SELECT a.id, a.in_time
FROM `ca_attendance` AS a
LEFT JOIN (
    SELECT MAX(id) AS id, in_time
    FROM ca_attendance
    GROUP BY children_id
) AS b ON a.id = b.id AND a.in_time = b.in_time

Answer 1

SELECT a.id, a.in_time
FROM `ca_attendance` AS a
 JOIN (
    SELECT MAX(in_time) AS in_time, children_id
    FROM ca_attendance
    GROUP BY children_id

) AS b ON a.children_id = b.children_id and a.in_time = b.in_time