SQL query to retrieve the last record for all individual table elements

Question

I have a table of all sales defined as:

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 latest selling price for all items (since the price may change). I know that I can execute 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 latest selling price for all items ( ItemID is stored in a separate table of items) and insert them into a separate table. How can I get this data? I tried the following queries:

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

and then wrap this in an insert but don't get the correct data. Is there one query that I can execute to get the latest price for each item and insert it into the table defined as follows:

 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 a problem with the largest-n-group that often occurs when stack overflows.

 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;