Select first row for each group in PostgreSQL
Given a table seasons
:
id | team | points |
---|---|---|
1 | Liverpool | 82 |
2 | Liverpool | 84 |
3 | Brighton | 34 |
4 | Brighton | 28 |
5 | Liverpool | 79 |
We want to find the rows containing the maximum number of points per team.
The expected output we want is:
id | team | points |
---|---|---|
3 | Brighton | 34 |
2 | Liverpool | 84 |
From the SQL Editor, you can run a query like:
_10select distinct_10 on (team) id,_10 team,_10 points_10from_10 seasons_10order BY_10 id,_10 points desc,_10 team;
The important bits here are:
- The
desc
keyword to order thepoints
from highest to lowest. - The
distinct
keyword that tells Postgres to only return a single row per team.
This query can also be executed via psql
or any other query editor if you prefer to connect directly to the database.