This is one quick way to get the newest records of a group if you are grouping by the sku and stock.
select stock, sku, qty from table where concat(dt_tm,stock,sku) IN (
select concat(max(dt_tm), stock, sku) from table group by stock, sku )
Another approach (also assuming a current version of MySQL which supports subselects) is:
SELECT stock, sku, qty FROM table t1 WHERE dt_tim = ( SELECT max(dt_tm) FROM table t2 WHERE t1.stock = t2.stock AND t1.sku = t2.sku )
This should be more reliable than Jake's solution, which will have problems with some data values. To illustrate the possible problem, run the above query on the following data: dt_tm stock sku qty dt1 A BB 1 dt1 AB B 2
Jake's query will return a single row instead of two rows. (Sorry, Jake, I don't mean to put you on the spot!)
Bruce Feist
I have a table with stock-status transactions like ...
2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111
- the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction
The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ???
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
