Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
SQL FAQ Feat

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;

From 1 Month to 1 Hour! Create Your Data Models Faster in Datameer Then With SQL.

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 z

Up Next:

Read How to concatenate data from different rows of a table into a single variable in SQL?

More Resources We Think You Might Like

SQL FAQ Feat

How to concatenate data from different rows of ...

Let us consider the following table: CREATE TABLE fruits (     ID INT,     NAME VARCHAR(50) ) INS...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to select the first row of each GROUP BY in...

Let us create a table ‘orders’ to replicate the problem. CREATE TABLE orders (       ...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to find the duplicate data in a table in SQL?

Let us set up a ‘customers’ table with their email addresses: CREATE TABLE customers (     custom...

  • How-Tos FAQs
  • December 16, 2018