DISTINCT ON for unique per group in PostgreSQL

Introduction

To get one unique row per group from a table, picking the first row based on a specific order.

You want to find the latest order for each customer.

You need the first event recorded for each user.

You want one example product from each category.

You want to remove duplicates but keep the earliest entry per group.

Syntax

PostgreSQL

SELECT DISTINCT ON (group_column) group_column, other_columns
FROM table_name
ORDER BY group_column, order_column [ASC|DESC];

DISTINCT ON works only in PostgreSQL.

The ORDER BY must include the same columns as DISTINCT ON first, to define which row to keep.

Examples

Gets the latest order for each customer by ordering dates descending.

PostgreSQL

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

Gets the cheapest product in each category.

PostgreSQL

SELECT DISTINCT ON (category) category, product_name, price
FROM products
ORDER BY category, price ASC;

Sample Program

This creates a table of orders, inserts some sample data, then selects the latest order per customer.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

INSERT INTO orders (customer_id, order_date) VALUES
(1, '2024-01-01'),
(1, '2024-02-01'),
(2, '2024-01-15'),
(2, '2024-01-10'),
(3, '2024-03-01');

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

Important Notes

Always include ORDER BY with DISTINCT ON to control which row you get per group.

DISTINCT ON returns the first row it finds per group based on ORDER BY.

Summary

DISTINCT ON helps pick one unique row per group in PostgreSQL.

Use ORDER BY to decide which row to keep.

Great for getting latest, earliest, or specific rows per group.