Select the First Row Per Group With DISTINCT ON

Use PostgreSQL's `DISTINCT ON (col)` to keep only the first row for each unique value of a column, with `ORDER BY` controlling which row within each group is considered first.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Get each customer's single highest-value order

Alice has two orders: 101 (amount 50) and 105 (amount 120). The goal is to keep only the highest-amount order per customer. For PostgreSQL, DISTINCT ON (customer_id) groups rows by customer_id and returns the first row from each group in the order defined by ORDER BY customer_id, amount DESC — so for Alice, the row with amount 120 comes first and is kept. Bob's highest-amount order is 102 (80 > 35). Carol has only one order. For all other engines, the ROW_NUMBER() window function assigns rank 1 to the highest-amount row within each customer_id partition; the outer query filters to rn = 1. Both approaches produce the same three rows. The ROW_NUMBER approach generalizes to any engine supporting window functions; the DISTINCT ON approach is more concise but PostgreSQL-only.

MySQL MariaDB SQL Server SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (
  customer_id INT,
  customer_name VARCHAR(20),
  order_id INT,
  amount INT
);

INSERT INTO
  orders
VALUES
  (1, 'Alice', 101, 50),
  (1, 'Alice', 105, 120),
  (2, 'Bob', 102, 80),
  (2, 'Bob', 106, 35),
  (3, 'Carol', 103, 200);

Validated query

SQL

SELECT
  customer_id,
  customer_name,
  order_id,
  amount
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (
        PARTITION BY
          customer_id
        ORDER BY
          amount DESC
      ) AS rn
    FROM
      orders
  ) t
WHERE
  rn = 1
ORDER BY
  customer_id;

Expected result

customer_id	customer_name	order_id	amount
1	Alice	105	120
2	Bob	102	80
3	Carol	103	200

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (
  customer_id INT,
  customer_name VARCHAR(20),
  order_id INT,
  amount INT
);

INSERT INTO
  orders
VALUES
  (1, 'Alice', 101, 50),
  (1, 'Alice', 105, 120),
  (2, 'Bob', 102, 80),
  (2, 'Bob', 106, 35),
  (3, 'Carol', 103, 200);

Validated query

SQL

SELECT DISTINCT
  ON (customer_id) customer_id,
  customer_name,
  order_id,
  amount
FROM
  orders
ORDER BY
  customer_id,
  amount DESC;

Expected result

customer_id	customer_name	order_id	amount
1	Alice	105	120
2	Bob	102	80
3	Carol	103	200

PostgreSQL uses DISTINCT ON (customer_id) with ORDER BY customer_id, amount DESC. All other engines use a ROW_NUMBER() subquery with PARTITION BY customer_id ORDER BY amount DESC. Results are identical.

Useful when

Where this command helps.

retrieving the most recent order per customer without a subquery or window function wrapper
deduplicating rows that share a key column and keeping the one with the highest or lowest value of another column

Explanation

What the command is doing.

DISTINCT ON (expr) is a PostgreSQL extension that returns one row per distinct value of the expression, choosing which row to keep based on the ORDER BY clause. Unlike GROUP BY, which requires aggregating all other columns, DISTINCT ON returns a complete row as-is. Unlike a ROW_NUMBER() subquery, it needs no wrapper query. The DISTINCT ON expressions must appear at the start of the ORDER BY clause; additional sort keys that follow control which row within each group is selected. This syntax is PostgreSQL-specific. MySQL, MariaDB, SQL Server, and SQLite achieve the same result with ROW_NUMBER() OVER (PARTITION BY col ORDER BY tie_breaker) = 1 in a derived table — this pattern is standard SQL and produces identical output.

Select the First Row Per Group With DISTINCT ON

Get each customer's single highest-value order

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.