Open navigation menu

DISTINCT for Unique Results

Uses DISTINCT and DISTINCT ON to eliminate duplicate rows and select unique rows based on specific column ordering for data deduplication.

SqlData Fetching

Code

snippet
-- Get unique cities where customers live
SELECT DISTINCT city, state, country
FROM customers
WHERE is_active = true
ORDER BY country, state, city;

-- DISTINCT ON: get one row per group (PostgreSQL specific)
-- Get the most recent order for each customer
SELECT DISTINCT ON (customer_id)
    customer_id,
    order_id,
    order_date,
    total_amount,
    status
FROM orders
WHERE status IN ('completed', 'shipped')
ORDER BY customer_id, order_date DESC;

-- Count unique values
SELECT
    COUNT(DISTINCT category_id) AS unique_categories,
    COUNT(DISTINCT supplier_id) AS unique_suppliers,
    COUNT(*) AS total_products
FROM products;

-- DISTINCT with multiple columns for unique combinations
SELECT DISTINCT
    p.category_id,
    p.supplier_id,
    COUNT(*) OVER (PARTITION BY p.category_id, p.supplier_id) AS combo_count
FROM products p
WHERE p.stock_quantity > 0
ORDER BY category_id, supplier_id;

-- Eliminate duplicates from a UNION
SELECT product_name FROM products
WHERE category_id = 1
UNION
SELECT product_name FROM discontinued_products
WHERE category_id = 1
ORDER BY product_name;

Related Examples