title: SQL sort order based on multiple column values
body: <p>We have data sorted like the format below. The first column is our system numbering, the second column is a back end system numbering, and the third column signifies that the rows with a ParentCol value actually below to the matching row of Col1. (So item 20 has two additional parts, item 30 and item 50.)</p>
  
  <pre><code>Col1      Col2      ParentCol
  10        100       NULL
  20        200       NULL
  30        201       20
  40        300       NULL
  50        202       20
  10        400       NULL
  ...
  </code></pre>
  
  <p>This is just a small example - there can sometimes be hundreds of rows per order.  The application isn't handling this situation well from the standpoint of viewing the data in the UI.  Ideally, I would like to have the content sorted like this, with our ID number (Col1) being the primary sorting, but recognizing that the back-end ID number (Col2) should immediately follow when there is a ParentCol value matching our ID:</p>
  
  <pre><code>Col1      Col2      ParentCol
  10        100       NULL
  10        400       NULL
  20        200       NULL
  30        201       20
  50        202       20
  40        300       NULL
  </code></pre>
  
  <p>Using T-SQL, how would I write a query that returns the data as I need it? Because of the various numbering schemes and possible valid duplicate values in our numbering scheme, Col1, (but not the back-end column, Col2), I haven't been able to figure this one out.</p>
  
  <p>Thank you,</p>
  
  <p>Scott</p>