SQL排序父行值

Question

假设表格如下：

ID | Name   | Time  | Parent 
1 | Orange  | 1493596800 | 0 
2 | Apple  | 1483228800 | 0 
3 | Red Apple | 1493596800 | 2 
4 | Yellow Apple | 1493769600 | 2 

我想降子行的时间，我的表排序和过滤的行使得家长必须零。

例如：

SELECT * 
    FROM MyTable as mt1 
WHERE Parent = 0 
Order 
    BY 
    (SELECT mt2.Time 
     FROM MyTable mt2 
     WHERE mt2.Parent = mt1.ID 
    Order BY mt2.Time DESC 
     Limit 1 
    ) DESC 

**输出必须是：**

2 | Apple  | 1483228800 | 0 
1 | Orange  | 1493596800 | 0 

Answer 1

SELECT p.ID, p.Name, MAX(c.Time) AS NewestChildTime, p.Parent 
FROM MyTable p 
LEFT OUTER JOIN MyTable c 
ON c.Parent = p.ID 
WHERE p.Parent = 0 
GROUP BY p.ID, p.Name, p.Parent 
ORDER BY NewestChildTime DESC 

Answer 2

select a.* from MyTable a 
left join 
(
select a.name,max(b.time) time,b.parent from MyTable a 
join MyTable b on b.parent=a.id 
where b.parent>0 group by a.name,b.parent 
) b on b.parent=a.id 
where a.parent=0 
order by b.time desc