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.

{ name: "PostgreSQL", version: "DISTINCT ON / window fns" },
{ name: "Supabase", version: "view / RPC" },
]} />

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 ORDER BY must lead with the same column(s) as DISTINCT ON, then add the tiebreaker that decides the winner. From the Postgres docs: "The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s)" and "the 'first row' of each set is unpredictable unless ORDER BY is used."

So ORDER BY customer_id, created_at DESC = "group by customer, and within each group the newest order wins." Drop the created_at DESC and the result becomes non-deterministic.

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.

Which approach wins depends on your data and indexes — there is no universal answer. The single most useful index for all three is a composite on (group_col, tiebreaker DESC). Confirm the plan with EXPLAIN ANALYZE rather than trusting a blog (including this one).

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 })

wrong="Pulling every row to the client and deduping in JS — slow, costly, and breaks pagination."
right="Push DISTINCT ON into a view or RPC; the database returns one row per group already."
/>

• You need it filtered by per-request parameters — a plain view can't take args; use an RPC function instead.
• Your table is huge with no index on (group_col, tiebreaker) — all three approaches degrade to a full sort. Add the index first.

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().

Related Articles

• How to Get a Row Count in Supabase
• Return the Inserted Row ID in supabase-js
• Supabase Postgres Functions & Triggers Guide
• How to Query Using JOIN in Supabase

Frequently Asked Questions

Can supabase-js do SELECT DISTINCT ON?

No — the query builder has no DISTINCT method. Maintainers recommend a database VIEW (query it like a table) or an RPC function (call with rpc()).

How do I get the latest row per group in Postgres?

SELECT DISTINCT ON (group_col) * FROM t ORDER BY group_col, created_at DESC. The ORDER BY must start with the DISTINCT ON column, then the tiebreaker that picks the winner.

DISTINCT ON or ROW_NUMBER()?

DISTINCT ON is most concise. ROW_NUMBER() in a subquery is more portable and supports top-N. LATERAL can be fastest with few groups and a good index. Measure with EXPLAIN ANALYZE.

---

Originally published at https://www.iloveblogs.blog