Why DISTINCT ON for unique per group in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could find the first unique record per group with just one simple command?

The Scenario

Imagine you have a big list of orders from many customers, and you want to find just the first order each customer made. Doing this by hand means scanning through all orders, checking each customer, and picking the earliest one.

The Problem

Manually sorting and filtering through thousands of orders is slow and tiring. It's easy to make mistakes, like missing some customers or picking the wrong order. This wastes time and causes frustration.

The Solution

Using DISTINCT ON lets you quickly grab the first unique record per group, like the earliest order per customer, with a simple and clear command. It saves time and avoids errors.

Before vs After

✗ Before

SELECT * FROM orders WHERE order_id IN (SELECT MIN(order_id) FROM orders GROUP BY customer_id);

✓ After

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

What It Enables

This lets you easily get one unique record per group, making data summaries and reports fast and reliable.

Real Life Example

A store wants to send a welcome gift to each customer's first purchase. Using DISTINCT ON, they quickly find each customer's earliest order without complex steps.

Key Takeaways

Manually finding unique records per group is slow and error-prone.

DISTINCT ON simplifies getting the first unique record per group.

This makes data tasks faster, clearer, and less frustrating.