CODEWITHSUNDEEP
mysqlsql
sta.ck
sta.ck

Reputation: 23

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?

Upvotes: 2

Views: 263

Answers (3)

sta.ck
sta.ck

Reputation: 23

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

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.

Upvotes: 0

ishegg
ishegg

Reputation: 9947

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

Upvotes: 1

Related Questions