SQL query to get latest record for all distinct items in a table
- by David Buckley
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 | |
+-------------------+---------------------+------+-----+---------+-------+
