Max Function in MySQL not working the way I thought it would

Question

Basically, I am querying 3 columns in MySQL, Item_id, Date, and New_Qty. When I use the max function on the date column, the other columns all display their maxes for the same date range.

SELECT `item_id` , max(date) AS max_date ,`new_qty` 
FROM `item_warehouse_link_history`
WHERE warehouse_id =1
AND item_id=1000
AND DATE
BETWEEN '2010-10-01 12:00:00'
AND '2010-10-07 12:00:00'

So, while I'm trying to find "new_qty" for the latest date, the latest date pops up just fine, but the "new_qty" is the max for the entire range.

Any help is appreciated!

Answer 1

Use:

SELECT x.item_id,
       x.date,
       x.new_qty
  FROM ITEM_WAREHOUSE_LINK_HISTORY x
  JOIN (SELECT t.item_id, 
               MAX(t.date) AS max_date
          FROM item_warehouse_link_history t
         WHERE t.warehouse_id = 1
           AND t.item_id = 1000
           AND t.date BETWEEN '2010-10-01 12:00:00'
                          AND '2010-10-07 12:00:00'
      GROUP BY t.item_id) y ON y.item_id = x.item_id
                                                      AND y.max_date = x.date

Aggregate functions (IE COUNT, MIN, MAX, SUM, etc) only operate on the value(s) supplied (in case you're combining the value of two or more columns before the aggregate function is applied) -- other columns are only a guaranteed relationship if the columns not mentioned in aggregate functions are stated in the GROUP BY clause.