I have a table
updates with various fields - for instance
votes1. Values in the table can look like:
matchnum time entrant1 votes1
1305 2010-02-06 00:03:08 apples 10
1305 2010-02-06 00:05:05 apples 15
1305 2010-02-06 03:06:05 apples 16
1307 2010-02-08 00:01:00 oranges 2
1307 2010-02-09 00:03:15 oranges 525
1308 2010-02-10 00:02:30 carrots 1
1308 2010-02-11 00:02:45 carrots 25
And so on, tens of thousands of rows total.
I'd like to select the row for each matchnum which has the highest number for votes1. I tried the following query:
select * from updates where 1 group by matchnum having votes1=max(votes1) order by matchnum asc
However this doesn't give me results for all the
matchnum values. For instance there are matchnum values of: 1305, 1307, 1308, 1313, 1337, 1338, 1353, 1357, 1361, 1363, 1365, 1610, 1622, 1623, 1624, 1625, 1626, 1627, 1628, 1629, 1630, 1631, 1632
However, of those, the query result only gives data for 1305, 1313, 1337, 1353, 1363, 1625, and 1626.
In total there are 820 distinct values for matchnum yet this query's result only has 193 rows.
Is there something wrong or missing with the query? Why is it not showing rows for each distinct
matchnum value that is present in the table?
FROM ( SELECT matchnum
, MAX(votes1) AS max_votes
BY matchnum ) AS m
JOIN updates AS t
ON t.matchnum = m.matchnum
AND t.votes1 = m.max_votes1
Thank you very much!! That's very helpful to me. (minor edit - m.max_votes1 should be m.max_votes)
Anyone know what the problem with the logic for my query is?
nicely spotted on the typo
the problem with your query was invalid grouping
you had GROUP BY matchnum, and thus there is only one row produced for each matchnum, but since votes1 was not included in the GROUP BY (mysql calls this a "hidden" column), the value chosen for votes1 in the result row is indeterminate and thus it's not always going to equal MAX(votes1)
read more here: GROUP BY and HAVING with Hidden Columns
mysql goes ahead and tries to execute invalid sql anyway, which, in my opinion, causes way more problems, ~especially~ amongst developers who maybe are not completely familiar with how GROUP BY works, than what it solves (which is perhaps best left for another time)
Thanks for the useful explanation