How does the first row per group query work?

DISTINCT ON (customer_id) keeps the first row for each customer in the ORDER BY order. The ORDER BY must start with the DISTINCT ON column; order_date then decides which row is first and id breaks ties. The portable version numbers rows within each customer and keeps number 1; for the latest row instead, sort descending.

What commonly goes wrong with first row per group in SQL?

Without a tie-breaker, two orders on the same date make the 'first' row arbitrary and the result can change between runs.

First Row Per Group in SQL

First row per group picks one row from each group, such as each customer's first order or each user's latest login.

Example query

This example assumes: orders(id bigint, customer_id bigint, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT DISTINCT ON (customer_id)
       customer_id,
       id AS first_order_id,
       order_date,
       total_amount
FROM orders
ORDER BY customer_id, order_date, id;

Portable version (works in most databases)

SELECT customer_id, id AS first_order_id, order_date, total_amount
FROM (
  SELECT customer_id,
         id,
         order_date,
         total_amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, id) AS rn
  FROM orders
) t
WHERE rn = 1;

How it works

DISTINCT ON (customer_id) keeps the first row for each customer in the ORDER BY order.
The ORDER BY must start with the DISTINCT ON column; order_date then decides which row is first and id breaks ties.
The portable version numbers rows within each customer and keeps number 1; for the latest row instead, sort descending.

MySQL, BigQuery, SQL Server and SQLite differences

DISTINCT ON is PostgreSQL-only. In MySQL 8+, SQL Server, BigQuery and SQLite use the ROW_NUMBER version.
BigQuery also offers QUALIFY ROW_NUMBER() OVER (...) = 1 to avoid the subquery.

Common mistakes

Without a tie-breaker, two orders on the same date make the 'first' row arbitrary and the result can change between runs.
Selecting a non-grouped column with GROUP BY and MIN is not the same: it returns the minimum, not that row's other columns.
Nulls in the sort column sort last in ascending order in PostgreSQL; check that a null date cannot be picked as 'first'.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

What was each customer's first order?
Show the most recent login for every user.