Database

Select first row for each group in PostgreSQL


Given a table seasons:

idteampoints
1Liverpool82
2Liverpool84
3Brighton34
4Brighton28
5Liverpool79

We want to find the rows containing the maximum number of points per team.

The expected output we want is:

idteampoints
3Brighton34
2Liverpool84

From the SQL Editor, you can run a query like:


_10
select distinct
_10
on (team) id,
_10
team,
_10
points
_10
from
_10
seasons
_10
order BY
_10
id,
_10
points desc,
_10
team;

The important bits here are:

  • The desc keyword to order the points 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.