How to return all information of a row with MAX or MIN value in a GROUP BY in SQL?
- How-Tos FAQs
- December 16, 2018
Suppose we have a table “score” as below, and we need all information on a row with the maximum score of each team.
CREATE TABLE score (
id INT,
team VARCHAR(50),
score INT,
gameGame DATETIME,
gameLocation VARCHAR(50)
);
INSERT INTO score
SELECT 1, 'Team 1', 2, '2020-03-19', 'Location a' UNION ALL
SELECT 2, 'Team 2', 3, '2020-04-16', 'Location b' UNION ALL
SELECT 3, 'Team 1', 6, '2020-05-24', 'Location c' UNION ALL
SELECT 4, 'Team 3', 1, '2020-02-16', 'Location e' UNION ALL
SELECT 5, 'Team 2', 9, '2020-10-15', 'Location x' UNION ALL
SELECT 6, 'Team 3', 3, '2020-07-22', 'Location z';We can tackle the problem in two steps:
Step 1 : First, let’s select the high score of each team.
SELECT team, MAX(score) highScore
FROM score
GROUP BY team;Step 2 : Now, we need to use the above query to get all the team details.
-- Now let's select all detail of team high score using the above query
SELECT s.*
FROM score s
INNER JOIN (
SELECT team, MAX(score) highScore
FROM score
GROUP BY team
) sub
ON s.team = sub.team
AND s.score = sub.highScore
#Output
# id team score gameGame gameLocation
-------------------------------------------------------------------------------
3 Team 1 6 2020-05-24 00:00:00 Location c
5 Team 2 9 2020-10-15 00:00:00 Location x
6 Team 3 3 2020-07-22 00:00:00 Location zUp Next:
Read How to concatenate data from different rows of a table into a single variable in SQL?