Selecting the row having the max value for one field, grouping by another field

Databases
1

I have a table updates with various fields - for instance matchnum, time, entrant1, 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?

2 
SELECT t.matchnum 
     , t.time  
     , t.entrant1  
     , t.votes1
  FROM ( SELECT matchnum 
              , MAX(votes1) AS max_votes
           FROM updates
         GROUP
             BY matchnum ) AS m
INNER
  JOIN updates AS t
    ON t.matchnum = m.matchnum
   AND t.votes1 = m.max_votes1
3

Thank you very much!! :slight_smile: 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?

4

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)

5

Thanks for the useful explanation :slight_smile: