Chapter 19: PostgreSQL SELECT DISTINCT

Part 1: What is SELECT DISTINCT?

The SELECT DISTINCT clause removes duplicate rows from your query results, returning only unique combinations of the selected columns.

The Basic Syntax

When you use DISTINCT, PostgreSQL compares each row of your result set and eliminates duplicates, showing each unique combination only once.

DISTINCT vs. ALL

By default, SELECT includes ALL (though we rarely write it explicitly):

Part 2: Setting Up Our Example Data

Let’s create a rich dataset to explore all the nuances of DISTINCT. Imagine we’re running an online store and tracking customer orders:

Part 3: Basic DISTINCT Usage

1. Distinct Values in a Single Column

Let’s find all the unique customer states:

Result:

Since all our customers are in Texas, we get just one row. Not very exciting, but it shows how DISTINCT collapses duplicates.

2. Distinct Customer Cities

Result:

Even though Austin appears many times in the table, DISTINCT shows it only once.

3. Counting Distinct Values

Often, we want to know how many distinct values exist:

Result:

This tells us we have customers in 4 different cities (all in Texas), selling products from 7 categories, using 2 shipping methods.

Part 4: DISTINCT on Multiple Columns

Here’s where DISTINCT gets really powerful. When you specify multiple columns, DISTINCT considers the combination of values across those columns.

1. Unique Customer Locations

Find all unique combinations of customer city and state:

Result:

This shows each unique city-state pair. If we later had customers from different states with the same city name, we’d see multiple rows for that city.

2. Unique Product Orders

Find all unique combinations of product category and product name:

Result:

Even though “Mouse” appears in multiple orders, it appears only once here because the combination (Electronics, Mouse) is unique.

3. Understanding the Combination Logic

This is crucial: DISTINCT looks at the entire row of selected columns. For example:

Partial Result:

This shows which product categories have been ordered from each city.

Part 5: DISTINCT with NULL Values

NULL values have special behavior with DISTINCT. PostgreSQL treats all NULLs as equal for DISTINCT purposes.

Let’s add some NULL values to see this:

Result:

Notice: all three NULL values collapsed into a single NULL row. This is important – DISTINCT treats all NULLs as identical.

Part 6: DISTINCT ON – PostgreSQL’s Special Feature

PostgreSQL offers a powerful extension to standard SQL: DISTINCT ON. This keeps the “first” row for each unique value in specified columns.

Basic DISTINCT ON Syntax

⚠️ Critical: The columns in DISTINCT ON must be the first columns in ORDER BY.

Example 1: Latest Order for Each Customer

Find the most recent order for each customer:

Result:

For Alice Johnson, we see her most recent order (Mouse Pad on 2024-02-01) instead of her earlier orders.

Example 2: Most Expensive Product in Each Category

First, let’s add price information:

Now find the most expensive product in each category:

Result:

For each category, we get the product with the highest price.

Part 7: DISTINCT vs. GROUP BY

Often, you can achieve similar results with DISTINCT and GROUP BY. Understanding the difference helps you choose the right tool.

Same Result, Different Approaches

Both return the same unique city-state combinations.

When GROUP BY is Better

GROUP BY becomes essential when you need aggregations:

Result:

Performance Considerations

• DISTINCT is often optimized for simple uniqueness checks

• GROUP BY is optimized for aggregations but can be used for uniqueness

• For large datasets, test both approaches to see which performs better

Part 8: Real-World Examples

Example 1: Customer Analytics Dashboard

Example 2: Inventory Planning Report

Find which product categories are sold in each city:

Result:

Example 3: Shipping Method Analysis

Analyze shipping methods by city and status:

Example 4: Customer Purchase History Summary

Part 9: DISTINCT with Window Functions

Advanced users can combine DISTINCT with window functions for sophisticated analytics:

Running Count of Distinct Customers

Distinct Products Per Customer Over Time

Part 10: Common Pitfalls and Solutions

Pitfall 1: DISTINCT Doesn’t Work as Expected with ORDER BY

Solution: Use DISTINCT ON with proper ordering:

Pitfall 2: Forgetting NULLs in DISTINCT

Pitfall 3: DISTINCT with Large Text Fields

Pitfall 4: Assuming DISTINCT Orders Results

Part 11: Performance Optimization

Creating Indexes for DISTINCT Queries

Analyzing Query Performance

Use EXPLAIN ANALYZE to see how PostgreSQL executes your DISTINCT queries:

This shows whether PostgreSQL is using indexes, how many rows it’s processing, and where the time is spent.

Summary: The DISTINCT Philosophy

SELECT DISTINCT is your tool for finding the unique “fingerprints” in your data:

1. Single column DISTINCT – “What are all the unique values in this column?”

2. Multi-column DISTINCT – “What are all the unique combinations of these columns?”

3. DISTINCT ON (PostgreSQL special) – “Give me the first row for each unique key”

4. COUNT(DISTINCT) – “How many unique values exist?”

Remember:

• DISTINCT removes duplicate rows, not duplicate values in a single column (unless it’s the only column)

• NULLs are all considered equal for DISTINCT

• Always use ORDER BY with DISTINCT ON

• Consider GROUP BY when you need aggregations

• Test performance on large datasets

The beauty of DISTINCT is that it helps you understand the variety and uniqueness in your data. It answers questions like “How many different cities do our customers come from?” and “What product categories has each customer purchased?”

Master DISTINCT, and you’ll have a powerful lens for viewing the unique patterns hidden in your database!

Would you like me to elaborate on any specific aspect of DISTINCT, such as more complex DISTINCT ON scenarios, performance tuning, or integration with other SQL features?