MySQL MAX() function returns wrong associated value

Question

I've tried various queries and read different answers to this and nothing seems to give EXACTLY what I'm looking for. Advice is appreciated.

I have a table with items, dates and quantities. For each item, I need the latest date and associated quantity. The table looks like this:

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

After running this query:

SELECT item, MAX(date), qty
FROM table
GROUP BY item

I get this:

item   date         qty
1234   2015-02-13   300

Instead of a qty of 500. I've tried a couple of different solutions including this and this. The first one gave all the records with NULL in date. The second one seems to work, until I try to do SUM(qty) (there can be multiple qty for an item on any given date). Since this solution sorts by date descending, and does not actually cut out any data, it doesn't help in this scenario.

Why does my original query not work? Any other ideas?

Thanks!

score 2 · Answer 1 · answered Mar 17 '15 at 02:54

Your query doesn't work because mysql just returns an arbitrary row when using group by like this. In fact, most databases won't even run this.

There are a couple alternative options -- here's one joining the table back to itself:

SELECT t1.item, t1.date, t1.qty
FROM table t1 
    JOIN (select item, max(date) maxdate
          from table
          group by item) t2 ON t1.item = t2.item 
    and t1.date = t2.maxdate

score 0 · Accepted Answer · answered Mar 17 '15 at 02:54

0

Of course it does. You need a join to get what you want:

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

Your query should fail because qty is not in the group by. MySQL allows it, but any other database would return an error.

answered Mar 17 '15 at 02:54

Gordon Linoff

1,242,037
58
646
786

I think I had the wrong field somewhere - just redid it and it works! That's what you get for trying to work when you should be sleeping. – AldermanJaw Mar 17 '15 at 03:02
Maybe I don't understand how `max()` works. I want to see `qty` grouped by `itemnum`, how would grouping by `qty` help here? – AldermanJaw Mar 17 '15 at 03:08
@AldermanJaw . . . `max()` returns the maximum value of a column. It does not return the row with the maximum value. – Gordon Linoff Mar 17 '15 at 11:49

MySQL MAX() function returns wrong associated value

2 Answers2