Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

1.2 KB
Raw Blame
---
title: 'Select first row for each group in Postgres'
description: 'Postgres snippet for grabbing the first row in each distinct group by group'
footerHelpType: 'postgres'
---

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 |

Run the following SQL using the [SQL Editor](/dashboard/project/_/sql) in the Supabase Dashboard or with `psql` over a direct connection. To connect with `psql`, see [Direct connection](/docs/guides/database/connecting-to-postgres#direct-connection).

```sql
select distinct
  on (team) id,
  team,
  points
from
  seasons
order by
  team,
  points desc;
```

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.