Getting MAX value from a joined MySQL column based on original table row input

Question

The title might be a bit confusing, but I guess there's no easy way to say it. This is the problem:

I have two tables. One lists football matches, some 3.000 of them, each with separate ID. The second table holds betting odds, all in one table, with an ID in each row, and three columns Home, Draw and Away for odds.

There is no problem when I want to extract the max Home odds for a specific match (ID added to WHERE clause). Some 30 rows are checked and the max value is returned.

The problem arises when I want to check all the max values for each individual match, as all the Home odds are in the same column. So, for all matches the max value of the entire column will be returned, and not the one related to the match.

The fixtures table holds information about the matches. It's simple, 3 columns, plus three more for the top values which ought to be extracted from the other table:

match ID, event name, start date, TopHomeOdds, TopDrawOdds, TopAwayOdds.

The odds table has multiple rows for the same match.

match ID, event name, bookmaker, homeOdds, drawOdds, awayOdds

123456 TeamA-TeamB Bookmaker1 1.60 3.00 4.05

123456 TeamA-TeamB Bookmaker2 1.55 3.05 4.00

123456 TeamA-TeamB Bookmaker3 1.65 2.95 4.10

I want to extract the max values (in the above example it would be 1.65, 3.05, 4.10) so I can insert those values into the first table. Inserting is not a problem, getting the max values is. I suppose some clever JOIN query should be used, that's where one of you guys steps in...thanks in advance.

This is the code which inaccurately returns max values for all homeOdds, drawOdds and

awayOdds, so the output is something like:

123456 match1 startdate 29.00 4.00 13.50

234567 match2 startdate 29.00 4.00 13.50

345678 match3 startdate 29.00 4.00 13.50

All the results are identical because the max value hasn't been related to the match itself, but a general max value of the entire column is returned.

    SELECT fixtures.matchid AS eventid, fixtures.matchdate AS eventstartdate,
    fixtures.eventname AS eventname, MAX(odds.homeOdds) AS TopHomeOdds, 
    MAX(odds.drawOdds) AS TopDrawOdds, MAX(odds.awayOdds) AS TopAwayOdds 
    FROM
    fixtures
    INNER JOIN odds ON odds.matchid=fixtures.matchid

The last part, the general INNER JOIN, is the problem. I want it to be selective regarding the match ID and I want it only to return max value for a column which has a row ID which relates to the match in the first table.

Dan, check here for some good answers: http://stackoverflow.com/questions755918. Particularly the answer from bobince. — Perception, Nov 20 '12 at 12:13

score 0 · Answer 1 · answered Nov 20 '12 at 12:15

Took me less time to find the solution than to post the question.

If GROUP BY eventid is added to the query, it works like a charm.

    SELECT fixtures.matchid AS eventid, fixtures.matchdate AS eventstartdate,
    fixtures.eventname AS eventname, MAX(odds.homeOdds) AS TopHomeOdds, 
    MAX(odds.drawOdds) AS TopDrawOdds, MAX(odds.awayOdds) AS TopAwayOdds 
    FROM
    fixtures
    INNER JOIN odds ON odds.matchid=fixtures.matchid
    GROUP BY eventid

Getting MAX value from a joined MySQL column based on original table row input

1 Answers1