MySQL MAX() function returns wrong associated value -

i've tried various queries , read different answers , nothing seems give i'm looking for. advice appreciated.

i have table items, dates , quantities. each item, need latest date , associated quantity. table looks this:

item   date         qty 1234   2014-12-22   300 1234   2015-02-13   500 

after running query:

select item, max(date), qty table group item 

i this:

item   date         qty 1234   2015-02-13   300 

instead of qty of 500. i've tried couple of different solutions including this , this. first 1 gave records null in date. second 1 seems work, until try sum(qty) (there can multiple qty item on given date). since solution sorts date descending, , not cut out data, doesn't in scenario.

why original query not work? other ideas?

thanks!

of course does. need join want:

select t.* table t join      (select item, max(date) maxd       table t       group item      ) tmax      on t.item = tmax.item , t.date = tmax.maxd; 

your query should fail because qty not in group by. mysql allows it, other database return error.