sta.ck sta.ck - 1 month ago 13
SQL Question

MySQL MAX() GROUP BY 3 tables

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?

Answer Source

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.