SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    New Jersey
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I have a table `updates` with various fields - for instance `matchnum`, `time`, `entrant1`, `votes1`. Values in the table can look like:
    Code:
    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:
    Code SQL:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    New Jersey
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    New Jersey
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the useful explanation


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •