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

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.