How to Resolve 'DISTINCT ON Expressions Must Match Initial ORDER BY Expressions' in PostgreSQL?

PostgreSQL's DISTINCT ON clause simplifies retrieving the first row for each unique value within a set. However, a common pitfall arises when the DISTINCT ON expression doesn't align with the initial ORDER BY expression.

DISTINCT ON Expression Mismatch Error

This error frequently occurs:

SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM purchases
WHERE purchases.product_id = 1
ORDER BY purchases.purchased_at DESC

Resulting in:

<code>PG::Error: ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions</code>

The Solution: Aligning DISTINCT ON and ORDER BY

PostgreSQL's documentation mandates that the DISTINCT ON expression(s) must mirror the leftmost ORDER BY expression(s). The solution is straightforward: reorder your ORDER BY clause:

SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM purchases
WHERE purchases.product_id = 1
ORDER BY address_id, purchases.purchased_at DESC

Alternative Methods: Bypassing address_id Ordering

If you need to avoid ordering by address_id, consider these alternatives:

Method 1: The "Greatest N per group" Approach

This method efficiently finds the most recent purchase for each address_id:

SELECT t1.* 
FROM purchases t1
JOIN (
    SELECT address_id, max(purchased_at) as max_purchased_at
    FROM purchases
    WHERE product_id = 1
    GROUP BY address_id
) t2
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC

Method 2: Nested Query for PostGreSQL

This approach uses a subquery to achieve the desired result while maintaining the purchased_at ordering:

SELECT * 
FROM (
  SELECT DISTINCT ON (address_id) *
  FROM purchases 
  WHERE product_id = 1
  ORDER BY address_id, purchased_at DESC
) t
ORDER BY purchased_at DESC

These alternatives provide flexibility when you don't want to prioritize address_id in the final output ordering. They effectively address the "most recent purchase per address" problem without directly violating the DISTINCT ON constraint.

The above is the detailed content of How to Resolve 'DISTINCT ON Expressions Must Match Initial ORDER BY Expressions' in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!