title: Access SQL query: find the row with the most recent date for each distinct entry in a table
body: <p>All, </p>
  
  <p>I'm sure this is a pretty simple SQL query question, but I'm sure there's a good way, and a very BAD way, to do this. Left to my own devices, I'm liable to end up with the latter. So...</p>
  
  <p>I have a table in Access with data that looks like this:</p>
  
  <pre><code>ID      Value  As_of
  1173    156    20090601
  1173    173    20081201
  1173    307    20080901
  1173    305    20080601
  127     209    20090301
  127     103    20081201
  127     113    20080901
  127     113    20080601
  1271    166    20090201
  1271    172    20081201
  1271    170    20080901
  1271    180    20080601
  ...
  </code></pre>
  
  <p>What I'd like to get is the "Value" for each unique ID with the most recent "As Of" date (which is in YYYYMM format).</p>
  
  <p>So, my result set should look like this:</p>
  
  <pre><code>ID      Value    As_of
  1173    156      20090601
  127     209      20090301
  1271    166      20090201
  </code></pre>
  
  <p>Note that different IDs will have different "As Of" dates. In other words, I can't simply indentify the most recent as of globally, then select every row with that date.</p>
  
  <p>For what it's worth, this table has about 200,000 total rows, and about 10,000 unique IDs.</p>
  
  <p>Many thanks in advance!</p>