MySQL - Max() return wrong result

Question

I tried this query on MySQL server (5.1.41)...

SELECT max(volume), dateofclose, symbol, volume, close, market FROM daily group by market

I got this result:

max(volume) dateofclose symbol  volume  close   market
287031500     2010-07-20  AA.P    500     66.41   AMEX
242233000     2010-07-20  AACC    16200   3.98    NASDAQ
1073538000   2010-07-20  A       4361000 27.52   NYSE
2147483647   2010-07-20  AAAE.OB 400     0.01    OTCBB
437462400     2010-07-20  AAB.TO  31400   0.37    TSX
61106320       2010-07-20  AA.V    0       0.24    TSXV

As you can see, the maximum volume is VERY different from the 'real' value of the volume column?!?

The volume column is define as int(11) and I got 2 million rows in this table but it's very far from the max of MyISAM storage so I cannot believed this is the problem!? What is also strange is data get show from the same date (dateofclose). If I force a specific date with a WHERE clause, the same symbol came out with different max(volume) result. This is pretty weird...

Need some help here!

UPDATE :

Here's my edited "working" request:

SELECT a.* FROM daily a 
INNER JOIN ( 
SELECT market, MAX(volume) AS max_volume 
FROM daily 
WHERE dateofclose = '20101108' 
GROUP BY market 
) b ON 
a.market = b.market AND 
a.volume = b.max_volume

So this give me, by market, the highest volume's stock (for nov 8, 2010).

What do you mean by "real" value? The MAX is returning the highest volume value per market value. — OMG Ponies, Nov 09 '10 at 02:39
Yes but the corresponding column doesn't seem to match. Adding the volume column to select show this. — Steve S., Nov 09 '10 at 03:02
It seems like a bug that MySql doesn't error if you SELECT columns that are not in the GROUP BY. (SQL Server will throw an error.) — beach, Nov 09 '10 at 05:11

score 7 · Accepted Answer · edited May 23 '17 at 12:22

7

As you can see, the maximum volume is VERY different from the 'real' value of the volume column?!?

This is because MySQL rather bizarrely doesn't GROUP things in a sensical way.

Selecting MAX(column) will get you the maximum value for that column, but selecting other columns (or column itself) will not necessarily select the entire row that the found MAX() value is in. You essentially get an arbitrary (and usually useless) row back.

Here's a thread with some workarounds using subqueries: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

edited May 23 '17 at 12:22

Community

1
1

answered Nov 09 '10 at 02:51

ceejayoz

176,543
40
303
368

That does the trick! I applied the recipe and this is my resulting query: SELECT tt.* from daily tt inner join ( select dateofclose, symbol, volume, close, market, MAX(volume) as MaxVolume FROM daily group by market) groupedtt on tt.market = groupedtt.market AND tt.volume = groupedtt.MaxVolume – Steve S. Nov 09 '10 at 03:14

score 2 · Answer 2 · edited May 23 '17 at 11:56

This is a subset of the "greatest n per group" problem. (There is a tag with that name but I am a new user so I can't retag).

This is usually best handled with an analytic function, but can also be written with a join to a sub-query using the same table. In the sub-query you identify the max value, then join to the original table on the keys to find the row that matches the max.

Assuming that {dateofclose, symbol, market} is the grain at which you want the maximum volume, try:

select
    a.*, b.max_volume
from daily a
join
(
    select
        dateofclose, symbol, market, max(volume) as max_volume
    from daily
    group by
        dateofclose, symbol, market
) b
on
    a.dateofclose = b.dateofclose
    and a.symbol = b.symbol
    and a.market = b.market

Also see this post for reference.

Humm, this doesn't work. After 30 sec of thinking, it return exactly the same number of results as there is row in this table! I clean up my request and it look like this : SELECT a.* FROM daily a INNER JOIN ( SELECT market, MAX(volume) AS max_volume FROM daily WHERE dateofclose = '20101108' GROUP BY market ) b ON a.market = b.market AND a.volume = b.max_volume — Steve S., Nov 10 '10 at 13:35

score 0 · Answer 3 · answered Nov 09 '10 at 02:34

0

Did you try adjusting your query to include Symbol in the grouping?

    SELECT max(volume), dateofclose, symbol, 
   volume, close, market FROM daily group by market, symbol

answered Nov 09 '10 at 02:34

Bob Palmer

4,714
2
27
31

This doesn't work. I got 24 963 result with still max(volume) different from the volume column. :-( – Steve S. Nov 09 '10 at 02:48

MySQL - Max() return wrong result

3 Answers3

Linked