PostgreSQL: Select Rows with Max Value in Column By Group

Often in PostgreSQL you may want to select the rows from a table that have the max value in a specific column, by group.

You can use the following syntax to do so:

SELECT DISTINCT ON (team)
    athleteid,
    team,
    position,
    points
FROM athletes
ORDER BY team, points DESC;

This particular example will select the rows with the max value in the points column, grouped by the values in the team column.

The following example shows how to use this syntax in practice.

Example: Select Rows with Max Value in Column By Group in PostgreSQL

Suppose that we create the following table named athletes that contains information about the points scored by various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  position TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Forward', 23);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Center', 40);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Forward', 14);
INSERT INTO athletes VALUES (0007, 'Hawks', 'Forward', 33);
INSERT INTO athletes VALUES (0008, 'Hawks', 'Center', 12);

-- view all rows in table
SELECT * FROM athletes;

 athleteid | team  | position | points 
-----------+-------+----------+--------
         1 | Mavs  | Guard    |     15
         2 | Mavs  | Guard    |     12
         3 | Mavs  | Forward  |     23
         4 | Mavs  | Center   |     40
         5 | Hawks | Guard    |     29
         6 | Hawks | Forward  |     14
         7 | Hawks | Forward  |     33
         8 | Hawks | Center   |     12

Suppose that we would like to select the row with the max value in the points column for each team.

We can use the following syntax to do so:

SELECT DISTINCT ON (team)
    athleteid,
    team,
    position,
    points
FROM athletes
ORDER BY team, points DESC;

 athleteid | team  | position | points 
-----------+-------+----------+--------
         7 | Hawks | Forward  |     33
         4 | Mavs  | Center   |     40

Notice that the resulting table contains the row with the max value in the points column for each team.

Feel free to use similar syntax to select the rows with the max value in a specific column of your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: Count Occurrences of Each Value in Column