title: SQL nested order by?
body: <p>I'm sure that this has been asked before, but I don't know what to call it exactly to find the answer.</p>
  
  <p>I have a table of categories and sub categories.  They each have an id and a parent id.  If it is a top level category, the parent id is 0.  Sub categories have the parent id set to the 
  category id of it's parent.</p>
  
  <pre><code>category_id          # The ID for this record
  category_name        # The name of the category
  parent_id            # The parent ID for this category
  display_order        # Order of categories within their grouping
  
  1 A  0 0     # First primary category
  2 a1 1 0     # Subcategory, parent is A, display_order is 0
  3 a2 1 1
  4 a3 1 2
  
  5 B  0 1     # Second primary category
  6 b1 5 0     # Subcategory, parent is B, display_order is 0
  7 b2 5 1
  8 b3 5 2
  </code></pre>
  
  <p>I'm trying to write an SQL query that will give me all of the categories in this order:</p>
  
  <p>A, a1, a2, a3, B, b1, b2, b3</p>
  
  <pre><code>SELECT * FROM categories ORDER BY display_order 
  </code></pre>
  
  <p>Is this possible in SQL, or will I need to use multiple queries</p>
  
  <p>Thanks,
  Brad</p>