title: Sort MySQL Query results by amount of recursion in foreign keys
body: <p>I have the following table:</p>
  
  <pre><code>+----+--------+
  | id | parent |
  +----+--------+
  |  1 | 4      |
  |  2 | 1      |
  |  3 | NULL   |
  |  4 | NULL   |
  |  5 | 2      |
  |  6 | 3      |
  +----+--------+
  </code></pre>
  
  <p>I want this table to be ordered like this:</p>
  
  <pre><code>+----+--------+------------------------------------------------------------+
  | id | parent |             Why it has to be ordered like this             |
  +----+--------+------------------------------------------------------------+
  |  5 | 2      | 5 has parent 2 has parent 1 has parent 4. So 3 rows above. |
  |  2 | 1      | 2 has parent 1 has parent 4. So 2 rows above.              |
  |  1 | 4      | 1 has parent 4. So 1 row above.                            |
  |  6 | 3      | 6 has parent 3. So 1 row above.                            |
  |  4 | NULL   | No parent. So 0 rows above.                                |
  |  3 | NULL   | No parent. So 0 rows above.                                |
  +----+--------+------------------------------------------------------------+
  </code></pre>
  
  <p>So I want to recursively count the ancestors of a row and sort on that. How can I do that?</p>
  
  <p>Edit: I'm on MySQL version 5.7.21.</p>