PostgreSQL SELECT DISTINCT

Key Concepts

SELECT DISTINCT country FROM customers;

SELECT DISTINCT city, country FROM customers;

SELECT DISTINCT email FROM users;

SELECT DISTINCT category FROM products;

🔹 Basic SELECT DISTINCT

The SELECT DISTINCT statement retrieves unique values from a column, automatically removing any duplicate entries. This is particularly helpful when analyzing data categories, customer locations, or product types where you only need to see each unique value once.

-- Get all unique countries
SELECT DISTINCT country FROM customers;

-- Result: Each country appears only once
-- USA
-- Canada
-- Mexico
-- UK

🔹 DISTINCT with Multiple Columns

When using DISTINCT with multiple columns, PostgreSQL returns unique combinations of all specified columns together. Each row in the result will have a unique combination of values across all selected columns, not just individual column uniqueness.

-- Get unique city and country combinations
SELECT DISTINCT city, country 
FROM customers;

-- Result: Unique pairs of city and country
-- New York, USA
-- Toronto, Canada
-- New York, Canada (different from New York, USA)

🔹 DISTINCT with WHERE Clause

You can combine DISTINCT with WHERE to filter data before removing duplicates. This allows you to get unique values from a specific subset of your data, making your queries more targeted and efficient for specific analysis needs.

-- Get unique cities only from USA
SELECT DISTINCT city 
FROM customers 
WHERE country = 'USA';

-- Get unique product categories with price > 100
SELECT DISTINCT category 
FROM products 
WHERE price > 100;

🔹 DISTINCT ON Expression

PostgreSQL offers DISTINCT ON to get the first row for each unique value in specified columns. This powerful feature lets you retrieve one representative row per group, which is useful for getting the latest order per customer or first entry per category.

-- Get first order for each customer
SELECT DISTINCT ON (customer_id) 
    customer_id, order_date, total 
FROM orders 
ORDER BY customer_id, order_date;

-- Get highest priced product per category
SELECT DISTINCT ON (category) 
    category, product_name, price 
FROM products 
ORDER BY category, price DESC;

🔹 Common Use Cases

SELECT DISTINCT is commonly used for data analysis, reporting, and creating dropdown lists in applications. It helps identify unique categories, locations, or values in your database, making it essential for understanding data distribution and creating user-friendly interfaces.