title: Nested order by/order by within order by in SQL
body: <p>I am looking to sort my sql results via an sql query with presumably some sort of nested order by/order by within an order by clause</p>
  
  <p>I have the following data:</p>
  
  <pre><code>TERM      USER      ITEM_NO     SCORE
  man       sam       2           NULL
  man       sam       1           170
  man       jerry     1           100 
  man       jerry     2           NULL
  man       sam       3           NULL
  </code></pre>
  
  <p>and I wish to obtain the following order for the results:</p>
  
  <pre><code>TERM      USER      ITEM_NO     SCORE
  man       sam       1           170
  man       sam       2           NULL
  man       sam       3           NULL
  man       jerry     1           100
  man       jerry     2           NULL
  </code></pre>
  
  <p>The results must be sorted firstly by the score (stored only in item_no 1 for each user) descending. However any further items created by that user for the seleted term must also be picked up and inserted directly following and in item_no order.</p>
  
  <p>My current query looks like this:</p>
  
  <pre><code>SELECT * FROM table WHERE term = 'man' ORDER BY SCORE DESC, ITEM_NO ASC
  </code></pre>
  
  <p>...however this simply results as follows:</p>
  
  <pre><code>TERM      USER      ITEM_NO     SCORE
  man       sam       1           170
  man       jerry     1           100
  man       sam       2           NULL
  man       jerry     2           NULL
  man       sam       3           NULL
  </code></pre>
  
  <p>Thank you for any suggestions.</p>