SQL query to get latest record for all distinct items in a table

Question

I have a table of all sales defined like:

mysql> 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    |       | 
+-------------------+---------------------+------+-----+---------+-------+

I need to get the last sale price for all items (as the price may change). I know I can run a query like:

SELECT price FROM saledata WHERE itemID = 1234 AND storeID = 111 ORDER BY saledate DESC LIMIT 1

However, I want to be able to get the last sale price for all items (the ItemIDs 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:

SELECT storeID, itemID, price FROM saledata WHERE itemID IN (SELECT itemID from itemmap) ORDER BY saledate DESC LIMIT 1

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:

mysql> 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    |       | 
+-------------------+---------------------+------+-----+---------+-------+

Answer 1

This is the greatest-n-per-group problem that comes up frequently on Stack Overflow.

INSERT INTO lastsale (StoreID, Price, ItemID)
  SELECT s1.StoreID, s1.Price, s1.ItemID
  FROM saledata s1
  LEFT OUTER JOIN saledata s2
    ON (s1.Itemid = s2.Itemid AND s1.SaleDate < s2.SaleDate)
  WHERE s2.ItemID IS NULL;