SQL query for parent children relation

Question

I am trying to write a sql query on bellow table.

╔════╦══════════╦═══════╗======╗======╗
║ ID ║   NAME   ║ CLASS ║PARENT║ DOB  ║
╠════╬══════════╬═══════╣======║======║
║ 1  ║   DAVID  ║ SPIN  ║      ║1     ║
║ 2  ║   AROON  ║ BIKE  ║ 1    ║1     ║
║ 3  ║   LEO    ║ YOGA  ║      ║2     ║
║ 4  ║   LIN    ║ CYC   ║ 1    ║2     ║
║ 5  ║   STEFA  ║ YOGA  ║      ║3     ║
║ 6  ║   GLORIA ║ RUNN  ║ 1    ║3     ║
╚════╩══════════╩═══════╝======╝======╝

And, output for this table should be like following

╔════╦════════╦═══════╗======╗======╗
║ ID ║ NAME   ║ CLASS ║PARENT║ DOB  ║
╠════╬════════╬═══════╣======║======║
║ 1  ║  DAVID ║ SPIN  ║      ║1     ║
║ 2  ║  AROON ║ BIKE  ║ 1    ║1     ║
║ 4  ║  LIN   ║ CYC   ║ 1    ║2     ║
║ 6  ║  GLORIA║ RUNN  ║ 1    ║3     ║
║ 3  ║  LEO   ║ YOGA  ║      ║2     ║
║ 5  ║  STEFAN║ YOGA  ║      ║3     ║
╚════╩════════╩═══════╝======╝======╝

So this is the explanation of the output
First parent David as his DOB is 1, 
--David three childrens sorted based on DOB
Then LEO as his DOB is 2
-- Leo do not have children[if he did, would be here as sorted on DOB] 
Then Stefan as his DOB is 3
--  Stefan do not have children [if he did, would be here as sorted on DOB] 

So what i tried?

SELECT * FROM user group by ID, PARENT ;

Above SQL, statement return items in parent children group but not doesn't maintain any order, when i add ORDER BY, SQL doesn't seems like honoring GROUP BY anymore.

Then i tried to do joining and end with two complete different tables where one contains all the parents and another one contains all children. UNION ALL on that two query returned expected data set but not in expected order.

Any thoughts?

UPDATE

Output should be
Pick entry [based on min time ].  
--use that id and find all of its children and placed them in sorted order
repeat for every row in the table

Note:

--parents are sorted based on DOB
--child's are also sorted based on DOB 
--DOB are valid timestamp 
--PARENT, ID field both are UUID and define as CHAR, PARENT reference to ID

SQL Fiddle

Similar on SO

Update 1

Query bellow

WITH RECURSIVE
top AS (
    SELECT * FROM (SELECT * FROM user WHERE PARENT is null ORDER BY dob LIMIT 1) 
    UNION
    SELECT user.NAME, user.PARENT, user.ID, user.CLASS, user.DOB FROM user, top WHERE user.PARENT=top.ID 
    ORDER BY user.dob
  ) SELECT * FROM top;

returning following output:

╔════╦════════╦═══════╗======╗======╗
║ ID ║ NAME   ║ CLASS ║PARENT║ DOB  ║
╠════╬════════╬═══════╣======║======║
║ 1  ║  DAVID ║ SPIN  ║      ║1     ║
║ 2  ║  AROON ║ BIKE  ║ 1    ║1     ║
║ 4  ║  LIN   ║ CYC   ║ 1    ║2     ║
║ 5  ║  GLORIA║ RUNN  ║ 1    ║3     ║
╚════╩════════╩═══════╝======╝======╝

Output is good for first parent. But, still couldn't figure out, how can i iterate through rest of parents and their children in sorted order.

Answer 1

Query

SELECT u1.*
FROM `user` u1
LEFT JOIN `user` u2
ON u1.PARENT = u2.ID
ORDER BY CASE WHEN u1.PARENT IS NULL THEN u1.DOB ELSE u2.DOB END
      || CASE WHEN u1.PARENT IS NULL THEN '' ELSE u1.DOB END;

Explanation

1. Alias u1 has all user details
2. Alias u2 has details of the parent where applicable. (A LEFT JOIN is used so these details will all be null if the u1 user doesn't have a parent.)
3. If the user doesn't have a parent, use its DOB on its own for ordering.
4. If the user has a parent, take the DOB of the user's parent and concatenate (append) the DOB of the user (child).

Results

The constructed values used to ORDER BY (which aren't actually needed in the SELECT) look like the rightmost column here:

╔════╦════════╦═══════╗======╗======╦════════╗
║ ID ║ NAME   ║ CLASS ║PARENT║ DOB  ║ORDER BY║
╠════╬════════╬═══════╣======║======╬════════║
║ 1  ║  DAVID ║ SPIN  ║      ║1     ║ 1      ║
║ 2  ║  AROON ║ BIKE  ║ 1    ║1     ║ 11     ║
║ 4  ║  LIN   ║ CYC   ║ 1    ║2     ║ 12     ║
║ 6  ║  GLORIA║ RUNN  ║ 1    ║3     ║ 13     ║
║ 3  ║  LEO   ║ YOGA  ║      ║2     ║ 2      ║
║ 5  ║  STEFAN║ YOGA  ║      ║3     ║ 3      ║
╚════╩════════╩═══════╝======╝======╩════════╝

Demo

See SQL Fiddle Demo.

Answer 2

Here is an ORDER BY which I believe is logically correct:

ORDER BY COALESCE(PARENT, DOB) ASC,
    CASE WHEN PARENT IS NULL THEN 0 ELSE DOB END

This answer assumes of course that you can actually use the PARENT and DOB columns in your query. You should generally not SELECT columns which are not either aggregates or specified in the GROUP BY clause.

If PARENT and DOB be defined as varchar then you can try casting them to a numeric type:

CAST(PARENT as integer)

You might want to change your table design so that these UUIDs are numeric type.