title: mysql nested query question
body: <p>I'm trying to design a mysql query to retrieve the row for each city with the most recent trans_date and trans_count.  I only want to return a single row per city.</p>
  
  <p>Table transactions</p>
  
  <pre><code>------------------
  id = integer
  trans_date = date
  trans_city = varchar
  trans_count = integer
  </code></pre>
  
  <p>Sample Data</p>
  
  <pre><code>--------------
  id       trans_date     trans_city     trans_count
  --       ----------     ----------     -----------
  1        2011-01-10     seattle        2104
  2        2011-04-15     seattle        2072
  3        2011-05-30     seattle        2057
  4        2010-04-27     houston        5622
  5        2010-04-30     houston        241
  6        2010-05-25     houston        261
  </code></pre>
  
  <p>Desired Query Results (one row per city with the most recent date and count for that city)</p>
  
  <pre><code>---------------------
  id       trans_date     trans_city     trans_count
  --       ----------     ----------     -----------
  3        2011-05-30     seattle        2057
  6        2010-05-25     houston        261
  </code></pre>
  
  <p>None of the samples I have found return this result set I'm looking for.  Any help appreciated.</p>
  
  <p>Thanks,</p>
  
  <p>-Scott</p>