Select the First Row per Group in Supabase Postgres

You want the most recent order per customer, or the highest-scoring row per team. You reach for GROUP BY... and hit a wall: GROUP BY returns aggregates, not the full non-aggregated row that achieved the max. This is the classic greatest-N-per-group problem, and PostgreSQL has a clean answer that supabase-js can't express directly.

The idiomatic way: DISTINCT ON#

PostgreSQL's DISTINCT ON "keeps only the first row of each set of rows where the given expressions evaluate to equal." The most-recent-row-per-group query:

SELECT DISTINCT ON (customer_id) customer_id, id, total, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

The portable way: ROW_NUMBER() in a subquery#

If you want top-N (not just top-1), or you prefer SQL that ports to other databases, use a window function:

SELECT customer_id, id, total
FROM (
  SELECT customer_id, id, total,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;   -- change to <= 3 for the three latest per customer

You must wrap it in a subquery. The Postgres docs are explicit: window functions "are permitted only in the SELECT list and the ORDER BY clause... They are forbidden... in WHERE clauses, [because] they logically execute after the processing of those clauses." Add a secondary column to the window's ORDER BY to break ties deterministically.

The often-fastest way: LATERAL#

When you have a small driving set of groups and a good index, a LATERAL join does an index top-1 lookup per group instead of scanning and sorting everything:

SELECT c.id AS customer, top.*
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.total
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 1
) AS top;

Use LEFT JOIN LATERAL ... ON true if you want customers with zero orders to still appear.

Running it from Supabase#

Here's the catch: the supabase-js query builder cannot issue DISTINCT ON (nor SELECT DISTINCT). In the official Supabase discussion, maintainers say it directly — "You can create a view to achieve it!" and "You can use a function for that. All filters work the same on functions (RPC) as on views."

So you write the SQL once in the database, then call it from the client.

Option A — a VIEW (best for fixed, parameter-free queries)#

create view latest_order_per_customer as
select distinct on (customer_id) customer_id, id, total, created_at
from orders
order by customer_id, created_at desc;

// query it exactly like a table
const { data, error } = await supabase
  .from('latest_order_per_customer')
  .select('*')

Option B — an RPC function (best when you need parameters)#

create or replace function latest_orders(min_total numeric)
returns setof orders
language sql
as $$
  select distinct on (customer_id) *
  from orders
  where total >= min_total
  order by customer_id, created_at desc;
$$;

const { data, error } = await supabase.rpc('latest_orders', { min_total: 100 })

Supabase even has a dedicated guide that hands you the DISTINCT ON SQL: Select first row for each group. Official references: PostgreSQL SELECT / DISTINCT ON, window functions, supabase-js rpc().

Paginating a DISTINCT ON or ROW_NUMBER() result#

Once you have one row per group, the next question is almost always "now show me page 2 of that." This is where it's easy to make a subtle mistake: applying LIMIT and OFFSET to the wrong query.

LIMIT/OFFSET on the outer, already-deduplicated result paginates groups — which is what you want:

SELECT customer_id, id, total, created_at
FROM (
  SELECT DISTINCT ON (customer_id) customer_id, id, total, created_at
  FROM orders
  ORDER BY customer_id, created_at DESC
) latest_per_customer
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

Putting LIMIT on the raw orders table before the DISTINCT ON/ROW_NUMBER() step instead paginates rows, which silently returns fewer groups than expected once a customer has more than one order in the scanned window — a bug that only shows up once real data has multiple rows per group, so it easily slips past testing with small seed data.

There's a real cost trade-off with OFFSET, independent of DISTINCT ON: Postgres still has to compute and discard every skipped row internally, so OFFSET 10000 on a large deduplicated set gets slower the deeper you page. For a table where you already track a monotonically increasing column (an id or created_at), keyset pagination — WHERE created_at < $last_seen_value ORDER BY created_at DESC LIMIT 20 applied to the deduplicated subquery — avoids that cost entirely and stays fast at any page depth, because the index does the seeking instead of the database counting past rows it will throw away.

If the underlying count matters too — showing "page 3 of 12" rather than just Next/Previous — that's a separate query against the same deduplicated subquery with COUNT(*) instead of LIMIT, not something DISTINCT ON gives you for free. For the building blocks of that count query itself, see How to Get a Row Count in Supabase; if pagination performance is the actual symptom you're chasing rather than a design question, the indexing checklist in Fix Slow Supabase Queries covers the same EXPLAIN ANALYZE workflow referenced above, applied end to end.