DISTINCT ON for unique per group in PostgreSQL - Mini Project: Build & Apply

Using DISTINCT ON to Get Unique Rows per Group in PostgreSQL

📖 Scenario: You work for a company that stores sales data in a PostgreSQL database. Each sale record includes the customer, the product sold, and the sale date. You want to find the most recent sale for each customer.

🎯 Goal: Build a SQL query using DISTINCT ON to select the latest sale record for each customer.

📋 What You'll Learn

Create a table called sales with columns customer (text), product (text), and sale_date (date).

Insert at least 5 rows with multiple sales per customer.

Write a query using DISTINCT ON (customer) to get the latest sale per customer.

Order the results by customer and sale_date DESC.

💡 Why This Matters

🌍 Real World

Finding the latest record per group is common in reporting, such as showing the most recent purchase per customer.

💼 Career

Database developers and analysts often use DISTINCT ON in PostgreSQL to simplify queries that require unique rows per group.

1

Create the sales table and insert data

Create a table called sales with columns customer (text), product (text), and sale_date (date). Then insert these exact rows: ('Alice', 'Book', '2024-01-10'), ('Bob', 'Pen', '2024-01-12'), ('Alice', 'Notebook', '2024-02-15'), ('Bob', 'Pencil', '2024-02-10'), ('Charlie', 'Eraser', '2024-01-20').

PostgreSQL

-- Create the sales table and insert the rows below
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add ordering for selecting latest sales

Write a SQL query that selects all columns from sales and orders the rows by customer ascending and sale_date descending. Assign this query to a variable or prepare it as a subquery for the next step.

PostgreSQL

CREATE TABLE sales (
    customer TEXT,
    product TEXT,
    sale_date DATE
);

INSERT INTO sales (customer, product, sale_date) VALUES
('Alice', 'Book', '2024-01-10'),
('Bob', 'Pen', '2024-01-12'),
('Alice', 'Notebook', '2024-02-15'),
('Bob', 'Pencil', '2024-02-10'),
('Charlie', 'Eraser', '2024-01-20');

-- Write a SELECT query ordering by customer ASC and sale_date DESC
-- Your code here

Need a hint?

Use ORDER BY customer ASC, sale_date DESC to sort the rows.

3

Use DISTINCT ON to get the latest sale per customer

Modify the previous query to use DISTINCT ON (customer) so that only the first row per customer is returned, which will be the latest sale due to the ordering.

PostgreSQL

CREATE TABLE sales (
    customer TEXT,
    product TEXT,
    sale_date DATE
);

INSERT INTO sales (customer, product, sale_date) VALUES
('Alice', 'Book', '2024-01-10'),
('Bob', 'Pen', '2024-01-12'),
('Alice', 'Notebook', '2024-02-15'),
('Bob', 'Pencil', '2024-02-10'),
('Charlie', 'Eraser', '2024-01-20');

-- Use DISTINCT ON (customer) to select the latest sale per customer
-- Your code here

Need a hint?

Use DISTINCT ON (customer) right after SELECT.

4

Complete the query with explicit column selection

Rewrite the query to select only customer, product, and sale_date columns using DISTINCT ON (customer) and the same ordering.

PostgreSQL

CREATE TABLE sales (
    customer TEXT,
    product TEXT,
    sale_date DATE
);

INSERT INTO sales (customer, product, sale_date) VALUES
('Alice', 'Book', '2024-01-10'),
('Bob', 'Pen', '2024-01-12'),
('Alice', 'Notebook', '2024-02-15'),
('Bob', 'Pencil', '2024-02-10'),
('Charlie', 'Eraser', '2024-01-20');

-- Select only customer, product, and sale_date columns with DISTINCT ON
-- Your code here

Need a hint?

List the columns explicitly after SELECT DISTINCT ON (customer).