0

I have database table with parent and child entries. Ex.

+---+-------+-------+---------+
|id |title  |parent | added   |
+---+-------+-------+---------+
|1  |title1 |0      |09:50    |
|2  |title2 |0      |09:55    |
|3  |title3 |0      |10:00    |
|4  |title4 |3      |10:05    |
|5  |title5 |1      |10:10    |
|6  |title6 |2      |10:15    |
+---+-------+-------+---------+

I need to select only parent = 0 but in the order of latest child entry added to the table.

SELECT * FROM table WHERE parent=0 ORDER BY added DESC

this only gives me following output

+---+-------+-------+---------+
|id |title  |parent | added   |
+---+-------+-------+---------+
|3  |title3 |0      |10:00    |
|2  |title2 |0      |09:55    |
|1  |title1 |0      |09:50    |
+---+-------+-------+---------+

But since id 4, 5, 6 added in related to parent order is 2, 1, 3 so my expectation is 

+---+-------+-------+---------+
|id |title  |parent | added   |
+---+-------+-------+---------+
|2  |title2 |0      |09:55    |
|1  |title1 |0      |09:50    |
|3  |title3 |0      |10:00    |
+---+-------+-------+---------+

What is the best way of writing this script?

FYI. Sorry for my poor English. If anyone can write this better way you are welcome. Thanks

AmilaDG
  • 186
  • 1
  • 3
  • 18
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Raymond Nijland Nov 19 '18 at 12:38

2 Answers2

2

If a parent can only have one child, you can do this with a simple LEFT JOIN to the children and then ordering the result by the child added value:

SELECT t1.*
FROM table1 t1
LEFT JOIN table1 t2 ON t2.parent = t1.id
WHERE t1.parent = 0
ORDER BY t2.added DESC

Output

id  title   parent  added
2   title2  0       09:55
1   title1  0       09:50
3   title3  0       10:00

SQLFiddle demo

If a parent may have multiple children, you will need to find the MAX(added) for the children of each parent and sort by that instead:

SELECT t1.*
FROM table1 t1
LEFT JOIN (SELECT parent, MAX(added) AS added 
           FROM table1 t2 
           GROUP BY parent) t2
    ON t2.parent = t1.id
WHERE t1.parent = 0
ORDER BY t2.added DESC

Alternate demo

Nick
  • 138,499
  • 22
  • 57
  • 95
1

Try in following way

SELECT T1.*
FROM TABLE T1
INNER JOIN
  (SELECT ID,parent
   FROM TABLE
   WHERE parent=0) T2 ON T1.parent = T2.ID
ORDER BY T1.added DESC


//Output

id  title   parent  added   
6   title6  2       10:15   
5   title5  1       10:10   
4   title4  3       10:05
Sadikhasan
  • 18,365
  • 21
  • 80
  • 122