title: Advanced grouping without using a sub query
body: <p>This is a sample of the data that I have.</p>
  
  <pre><code>-ID-        -Rank-      -Type-      -Status-    -Amount-
  1142474     2       Under Offer Approved    23
  1148492     1       Present     Current     56
  1148492     2       Under Offer Approved    3
  2273605     1       Present     Current     24
  </code></pre>
  
  <p>Where the ID is the same I only want the record with the highest rank. So the end result of the query.</p>
  
  <pre><code>-ID-        -Rank-      -Type-      -Status-    -Amount-
  1142474     2       Under Offer Approved    23
  1148492     1       Present     Current     56
  2273605     1       Present     Current     24
  </code></pre>
  
  <p>Now to get the original data set is an expensive operation, so <strong>I <em>don't</em> want to</strong> do a <strong>group by the ID</strong> and then <strong>mins the rank</strong> and <strong>then joins back</strong> onto the dataset again. Hence the query needs to do its work another way.</p>
  
  <p>Cheers
  Anthony</p>