CODEWITHSUNDEEP
mysqlsqlsortingsql-order-by
Nabi
Nabi

Reputation: 774

SQL sort parent rows in table based on child row values

Given the following table:

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

I want to sort my table by descending time of the child rows, and filter the rows such that the Parent must be zero.

For example:

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

**Output must be: **

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

Upvotes: 1

Views: 427

Answers (2)

maulik kansara
maulik kansara

Reputation: 1107

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

Upvotes: 1

Fred Sobotka
Fred Sobotka

Reputation: 5332

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

Upvotes: 1

Related Questions