PostgreSQL - DISTINCT Keyword

PostgreSQL - Selecting Unique Rows with DISTINCT

The DISTINCT keyword in a SELECT statement is used to remove duplicate rows from a query’s result set, returning only a single copy of each unique row. It is a fundamental tool for data analysis and reporting.

Syntax for SELECT DISTINCT

The basic syntax applies DISTINCT to all columns in the SELECT list:

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE [condition];

This will return rows where the combination of values in column1, column2, etc., is unique.

Example: Using DISTINCT

Consider a products table that lists products sold in different regions:

 id | product_name |   region   | price
----+--------------+------------+-------
  1 | T-Shirt      | North      | 20.00
  2 | T-Shirt      | South      | 20.00
  3 | Jeans        | North      | 50.00
  4 | Hat          | North      | 15.00
  5 | T-Shirt      | North      | 20.00
  6 | Jeans        | West       | 55.00

To get a unique list of all product names sold, you would use:

SELECT DISTINCT product_name FROM products;

This returns each product name only once, even though ‘T-Shirt’ and ‘Jeans’ appear multiple times in the table.

 product_name
--------------
 Hat
 Jeans
 T-Shirt
(3 rows)

If you apply DISTINCT to multiple columns, the combination must be unique:

SELECT DISTINCT product_name, region FROM products;

 product_name | region
--------------+--------
 T-Shirt      | North
 Jeans        | West
 T-Shirt      | South
 Hat          | North
 Jeans        | North
(5 rows)

PostgreSQL Extension: DISTINCT ON

PostgreSQL provides a powerful and unique extension to DISTINCT called DISTINCT ON (expression). This allows you to select the first row that is unique for a given expression, based on a specific ordering. It’s incredibly useful for ‘find the latest/greatest N for each group’ type of queries.

Syntax for DISTINCT ON

SELECT DISTINCT ON (expression1) column1, column2, ...
FROM table_name
ORDER BY expression1, expression2 [ASC | DESC];

Critical Rule: The expressions in the DISTINCT ON clause must match the leftmost expressions in the ORDER BY clause. This is how you control which row is considered the ‘first’ one to be returned for each distinct group.

Example: Find the Most Expensive Product in Each Region

Using the same products table, we can find the single most expensive product from each region.

SELECT DISTINCT ON (region)
    region, product_name, price
FROM products
ORDER BY region, price DESC;

How it works: The query first sorts all rows by region and then by price in descending order. Then, for each unique region, it picks the very first row it encounters, which due to the ordering, is the one with the highest price.

 region | product_name | price
--------+--------------+-------
 North  | Jeans        | 50.00
 South  | T-Shirt      | 20.00
 West   | Jeans        | 55.00
(3 rows)

Comparison with GROUP BY

Both DISTINCT and GROUP BY can be used to produce a set of unique values. However, they have different primary purposes:

• SELECT DISTINCT: Use when you simply want to remove duplicate rows from your result set. It’s a tool for filtering.
• GROUP BY: Use when you want to perform an aggregate function (like COUNT, SUM, AVG) on groups of rows. It’s a tool for aggregation and summarization.
• DISTINCT ON: Use for the specific case where you need to select entire representative rows from each group, not just an aggregated value.

Performance Considerations

Both DISTINCT and GROUP BY typically require a sort or hash operation on the result set, which can be computationally expensive on very large tables. Ensure that any columns used in these clauses are indexed where possible to improve performance.