Nice approach Bruce, but I too won't have any problems with your case because I am grouping by sku and stock in the sub-query.
Regards, Jake Johnson [EMAIL PROTECTED] ------------------------------------------------------------------ Plutoid - http://www.plutoid.com Shop Plutoid for the best prices on Rims and Car Audio Products On Thu, 19 Jun 2003, Bruce Feist wrote: > Jake Johnson wrote: > > >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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
