title: SQL query to get latest record for all distinct items in a table
body: <p>I have a table of all sales defined like:</p>
  
  <pre><code>mysql&gt; describe saledata;
  +-------------------+---------------------+------+-----+---------+-------+
  | Field             | Type                | Null | Key | Default | Extra |
  +-------------------+---------------------+------+-----+---------+-------+
  | SaleDate          | datetime            | NO   |     | NULL    |       | 
  | StoreID           | bigint(20) unsigned | NO   |     | NULL    |       | 
  | Quantity          | int(10) unsigned    | NO   |     | NULL    |       | 
  | Price             | decimal(19,4)       | NO   |     | NULL    |       | 
  | ItemID            | bigint(20) unsigned | NO   |     | NULL    |       | 
  +-------------------+---------------------+------+-----+---------+-------+
  </code></pre>
  
  <p>I need to get the last sale price for all items (as the price may change).  I know I can run a query like:</p>
  
  <pre><code>SELECT price FROM saledata WHERE itemID = 1234 AND storeID = 111 ORDER BY saledate DESC LIMIT 1
  </code></pre>
  
  <p>However, I want to be able to get the last sale price for all items (the <code>ItemID</code>s are stored in a separate item table) and insert them into a separate table.  How can I get this data?  I've tried queries like this:</p>
  
  <pre><code>SELECT storeID, itemID, price FROM saledata WHERE itemID IN (SELECT itemID from itemmap) ORDER BY saledate DESC LIMIT 1
  </code></pre>
  
  <p>and then wrap that into an insert, but it's not getting the proper data.  Is there one query I can run to get the last price for each item and insert that into a table defined like:</p>
  
  <pre><code>mysql&gt; describe lastsale;
  +-------------------+---------------------+------+-----+---------+-------+
  | Field             | Type                | Null | Key | Default | Extra |
  +-------------------+---------------------+------+-----+---------+-------+
  | StoreID           | bigint(20) unsigned | NO   |     | NULL    |       | 
  | Price             | decimal(19,4)       | NO   |     | NULL    |       | 
  | ItemID            | bigint(20) unsigned | NO   |     | NULL    |       | 
  +-------------------+---------------------+------+-----+---------+-------+
  </code></pre>