title: How do I preserve logical groupings while sorting in SQL?
body: <p>I have two tables which are related in a parent-child relationship</p>
  
  <pre><code>Sale
  sale_id | customer_name
  
  SaleItem
  item_id | sale_id | item_type
  </code></pre>
  
  <p>Each Sale record has one or more corresponding SaleItem records. My goal is to create a list of Sales with their corresponding SaleItems shown as children. I can do this with this query:</p>
  
  <pre><code>SELECT s.sale_id, s.customer_name, si.item_id, si.item_type 
  FROM Sale s, SaleItem si
  WHERE s.sale_id=si.sale_id
  ORDER BY s.sale_id, si.item_id
  </code></pre>
  
  <p>which yields something like this (blank rows are added for clarity of the problem):</p>
  
  <pre><code>sale_id | customer_name | item_id | item_type
  1       | Sam           | 1       | D
  1       | Sam           | 2       | B
  1       | Sam           | 3       | D
  
  2       | Nate          | 4       | A
  2       | Nate          | 5       | B
  
  3       | Mike          | 6       | B
  3       | Mike          | 7       | C
  
  4       | Nate          | 8       | C
  4       | Nate          | 9       | D
  </code></pre>
  
  <p>The ORDER BY clause results in a logical grouping of Sales with their ordered SaleItems. This provides me with the data I need to be able to code up a nice collapsible UI to represent the data:</p>
  
  <pre><code>-Sale 1 - Sam
   -B 
   -B 
   -D 
  +Sale 2 - Nate
  +Sale 3 - Mike
  -Sale 4 - Nate
   -C
   -D
  </code></pre>
  
  <p>This is all fine and good but what happens when I want to sort this list by item_type? I want to end up with a result set like this (blank rows are added for clarity of the problem):</p>
  
  <pre><code>sale_id | customer_name | item_id | item_type
  2       | Nate          | 4       | A
  2       | Nate          | 5       | B
  
  3       | Mike          | 6       | B
  3       | Mike          | 7       | C
  
  4       | Nate          | 8       | C
  4       | Nate          | 9       | D
  
  1       | Sam           | 1       | D
  1       | Sam           | 2       | B
  1       | Sam           | 3       | D
  </code></pre>
  
  <p>Please notice that each Sale and its corresponding SaleItems remain together. Basically what I need to do here is treat each set of Sale+SaleItems as a single element and intelligently sort them by any of the columns. Since we are essentially just sorting the Sales here and item_type applies to the SaleItem we just sort by the first SaleItem's item_type and maintain the original sorted order of the SaleItems within the Sale. I really hope that makes sense.</p>
  
  <p>I have a couple of questions:</p>
  
  <ol>
  <li>Am I going about this the wrong way? Since each Sale record is the "parent" of one or more SaleItem records the JOIN seems like overkill because I end up with the "parent" data being duplicated in each "child" row.</li>
  <li>How can I sort this data by the first item_type for each Sale and still keep each Sale's SaleItems together?</li>
  </ol>
  
  <p>Thanks a ton.</p>