You're right; you do avoid the problem with the specific sample data I gave you. Sorry about that! But, there are still potential problems because of the comparison of a single concatenated value instead of the separate SKU and STOCK values. In practice, they might not arise because of the formatting of the date/time field. Your subquery does yield, in effect, a good table of maximum date/times for each SKU/STOCK combination. But, you can get a false hit in it with the comparison if a dt_tm value isn't maximum for some stock, but there are other values dt_tm1 and stock1 such that concat(dt_tm,stock) = concat(dt_tm1, stock1) where dt_tm1 *is* maximum for stock1.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.
That was very abstract; I'll supply some more data, with bogus date/time values to illustrate my point.
dt_tm, stock, sku, qty
A, BB, C, 1
AB, BB, C, 2 -- note that AB is the max(dt_tm) for stock=BB,SKU=C; A is not the max
AB, B, C, 3 -- note that AB is the max(dt_tm) for stock=B,SKU=C
Subselect yields AB, BB, C and AB, B, C; concatenated they are ABBBC and ABBC
Select compares ABBC to the above and it matches, so BB, C, 1 is returned (incorrectly)
Select compares ABBBC to the above and it matches, so BB, C, 2 is returned
Select compares ABBC to the above and it matches, so B, C, 3 is returned
Bruce Feist
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 )
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
