MySQL MAX() GROUP BY 3 tables

Question

I have 3 tables:

CREATE TABLE IF NOT EXISTS sportsman (
    sportsman_id int NOT NULL AUTO_INCREMENT,
    sportsman_name varchar(255) NOT NULL,
    PRIMARY KEY (sportsman_id)
);

CREATE TABLE IF NOT EXISTS competition (
    competition_id int NOT NULL AUTO_INCREMENT,
    competition_name varchar(255) NOT NULL,
    PRIMARY KEY (competition_id)
);

CREATE TABLE IF NOT EXISTS results (
    competition_id int,
    sportsman_id int,
    result float,
    FOREIGN KEY (sportsman_id) REFERENCES sportsman(sportsman_id),
    FOREIGN KEY (competition_id) REFERENCES competition(competition_id)
);

Here is the sample data:

INSERT INTO `sportsman` (`sportsman_name`) VALUES ('sportsman1');
INSERT INTO `sportsman` (`sportsman_name`) VALUES ('sportsman2');

INSERT INTO `competition` (`competition_name`) VALUES ('competition1');
INSERT INTO `competition` (`competition_name`) VALUES ('competition2');

INSERT INTO `results` (`competition_id`, `sportsman_id`, `result`) VALUES ('1', '1', '20');
INSERT INTO `results` (`competition_id`, `sportsman_id`, `result`) VALUES ('1', '2', '25');

INSERT INTO `results` (`competition_id`, `sportsman_id`, `result`) VALUES ('2', '1', '18');
INSERT INTO `results` (`competition_id`, `sportsman_id`, `result`) VALUES ('2', '2', '23');

I need to get competition_name, max result, sportsman_name.

My query is:

SELECT 
    c.`competition_name`, 
    MAX(r.`result`), 
    s.`sportsman_name`
FROM `competition` c
INNER JOIN `results` r ON c.`competition_id` = r.`competition_id`
INNER JOIN `sportsman` s ON s.`sportsman_id` = r.`sportsman_id` 
GROUP BY c.`competition_name`;

It groups by competition_name, it finds max result. But it picks first sportsman_name.

Any suggestions?

Possible duplicate of [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — Uueerdo, Aug 16 '17 at 21:31

ishegg · Accepted Answer · 2017-08-16T22:39:44.750

1

GROUP BY the sportsman_name also to get the MAX(result) for every competitor in every competition.

SELECT 
    c.`competition_name`, 
    MAX(r.`result`), 
    s.`sportsman_name`
FROM `competition` c
INNER JOIN `results` r ON c.`competition_id` = r.`competition_id`
INNER JOIN `sportsman` s ON s.`sportsman_id` = r.`sportsman_id` 
GROUP BY c.`competition_name`, s.`sportsman_name`;

Link to a live demo to try it out

And if you want to only show the name of the highest scorer:

SELECT 
    c.`competition_name`, result, r.sportsman_id, sportsman_name
FROM
    `competition` c
        INNER JOIN
    `results` r ON c.`competition_id` = r.`competition_id`
        AND r.`sportsman_id` = (SELECT 
            rs.`sportsman_id`
        FROM
            results rs
        WHERE
            rs.`competition_id` = r.`competition_id`
        ORDER BY rs.`result` DESC
        LIMIT 1)
        INNER JOIN
    `sportsman` s ON s.sportsman_id = r.sportsman_id
GROUP BY c.`competition_name`;

Fiddle

edited Aug 16 '17 at 22:39

answered Aug 16 '17 at 21:23

ishegg

9,685
3
16
31

Thanks @Exomus for the Fiddle. – ishegg Aug 16 '17 at 21:38
I think the OP want the sportsman's name with the maximum result. – Gordon Linoff Aug 16 '17 at 22:18
Oh... I see. I've updated the answer to show how to achieve this as well. – ishegg Aug 16 '17 at 22:37
That's exactly what i needed. Thanks a lot. – sta.ck Aug 20 '17 at 18:56
Great! Happy to help. Remember to accept the answer if your issue is resolved. – ishegg Aug 20 '17 at 19:05
Just did it. I'm new to this site. Thanks again. – sta.ck Aug 20 '17 at 19:19
Awesome. Just being curious, which of the two options were you looking for? – ishegg Aug 20 '17 at 19:20

Gordon Linoff · Answer 2 · 2017-08-20T19:21:36.583

0

The simplest method in MySQL is to use group_concat()/substring_index():

SELECT c.`competition_name`, MAX(r.`result`), 
       SUBSTRING_INDEX(GROUP_CONCAT(s.`sportsman_name` ORDER BY r.result DESC), ',', 1) as sportspersons_name
FROM `competition` c INNER JOIN
     `results` r
     ON c.`competition_id` = r.`competition_id` INNER JOIN
     `sportsman` s
     ON s.`sportsman_id` = r.`sportsman_id` 
GROUP BY c.`competition_name`;

This has some limitations. First, if the competitor's name can have a comma, then you need another separator (such as '|'); that is a minor adjustment to the query.

Second, the internal buffer for GROUP_CONCAT() has a default maximum length of about 1,000 characters. For your sample data, this is unlikely to be a problem. But the limit is good to know about (it can be increased).

Two alternative methods get around this. One uses an additional query to get the maximum result and "join"s that back in. The second uses variables. In most databases, you would simply use ROW_NUMBER(), but MySQL does not support that ANSI-standard function.

edited Aug 20 '17 at 19:21

answered Aug 16 '17 at 21:22

Gordon Linoff

1,242,037
58
646
786

Why did you left last argument of GROUP_CONCAT empty? The query works fine if i put 1 as a last argument. `',', '1') as sportspersons_name` – sta.ck Aug 20 '17 at 19:03
@sta.ck . . . That should be `1` with no quotes. – Gordon Linoff Aug 20 '17 at 19:21
@sta.ck . . . This version should be a bit more efficient than the version using a `join` and `group by` in a subquery. – Gordon Linoff Aug 20 '17 at 19:54
It worked with quotes. Thank you for updating your answer. Your solution is really original. – sta.ck Aug 20 '17 at 19:56

score 0 · Answer 3 · answered Aug 20 '17 at 19:35

0

My colleague sent me another solution to my problem. Maybe it will help somebody

SELECT 
    c.competition_name,
    r.result,
    s.sportsman_name
FROM competition c 
JOIN (SELECT 
    MAX(result) result, competition_id
FROM results
GROUP BY competition_id 
) AS temp ON temp.competition_id = c.competition_id
JOIN results r ON r.result = temp.result AND r.competition_id = temp.competition_id
JOIN sportsman s ON s.sportsman_id = r.sportsman_id
WHERE temp.competition_id IS NOT NULL;

answered Aug 20 '17 at 19:35

sta.ck

23
2

Ah ok. Then I'll accept your answer because 1. you are from stackoverflow and 2. your solution is more universal. – sta.ck Aug 20 '17 at 19:54
I'm not from stack overflow :) just accept the answer which helped you the most. I asked a question on the other thread! Please answer if you can. – ishegg Aug 20 '17 at 19:56

MySQL MAX() GROUP BY 3 tables

3 Answers3