prestodb/presto · error · SemanticException

ORDER_BY_MUST_BE_IN_AGGREGATE

ORDER_BY_MUST_BE_IN_AGGREGATE

Error message

For aggregate function with DISTINCT, ORDER BY expressions must appear in arguments

What it means

When an aggregate function uses DISTINCT, Presto requires its ORDER BY expressions to be a subset of the function arguments, since ordering values that don't participate in the distinct set is ambiguous. AggregationAnalyzer throws ORDER_BY_MUST_BE_IN_AGGREGATE if any sort key is missing from the arguments (and isn't a matching column reference).

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java:425

                                node.getName(),
                                windowFunctions);
                    }

                    if (node.getOrderBy().isPresent()) {
                        List<Expression> sortKeys = node.getOrderBy().get().getSortItems().stream()
                                .map(SortItem::getSortKey)
                                .collect(toImmutableList());
                        if (node.isDistinct()) {
                            List<FieldId> fieldIds = node.getArguments().stream()
                                    .map(NodeRef::of)
                                    .map(columnReferences::get)
                                    .filter(Objects::nonNull)
                                    .flatMap(Collection::stream)
                                    .collect(toImmutableList());
                            for (Expression sortKey : sortKeys) {
                                if (!node.getArguments().contains(sortKey)
                                        && !(columnReferences.containsKey(NodeRef.of(sortKey)) && fieldIds.containsAll(columnReferences.get(NodeRef.of(sortKey))))) {
                                    throw new SemanticException(
                                            ORDER_BY_MUST_BE_IN_AGGREGATE,
                                            sortKey,
                                            "For aggregate function with DISTINCT, ORDER BY expressions must appear in arguments");
                                }
                            }
                        }
                        // ensure that no output fields are referenced from ORDER BY clause
                        if (orderByScope.isPresent()) {
                            for (Expression sortKey : sortKeys) {
                                verifyNoOrderByReferencesToOutputColumns(
                                        sortKey,
                                        REFERENCE_TO_OUTPUT_ATTRIBUTE_WITHIN_ORDER_BY_AGGREGATION,
                                        "ORDER BY clause in aggregation function must not reference query output columns");
                            }
                        }
                    }

                    // ensure that no output fields are referenced from ORDER BY clause

View on GitHub (pinned to 55bb57d202)

Solutions

Make the ORDER BY expression also appear in the argument list (order by the same aggregated expression)
Order the results after aggregation in an outer query instead of inside the aggregate
Drop the ORDER BY inside the aggregate if order doesn't matter
If multiple columns are needed, aggregate a composite (e.g. array_agg(DISTINCT ROW(a, b)) or array_agg(a ORDER BY a))

Example fix

// before
SELECT array_agg(DISTINCT customer ORDER BY order_date) FROM orders GROUP BY region
// after
SELECT array_agg(DISTINCT customer ORDER BY customer) FROM orders GROUP BY region

Defensive patterns

Strategy: validation

Validate before calling

// ensure DISTINCT aggregate ORDER BY keys are subsets of arguments
List<Expression> args = List.of(new Identifier("customer"));
List<Expression> sortKeys = List.of(new Identifier("order_date"));
if (!args.containsAll(sortKeys)) {
    throw new IllegalArgumentException("ORDER BY keys must appear in DISTINCT aggregate arguments");
}

Try / catch

catch (SemanticException e) { if (e.getCode() == ORDER_BY_MUST_BE_IN_AGGREGATE) { /* change ORDER BY to match an argument or sort in an outer query */ } throw e; }

Prevention

Only order DISTINCT aggregates by the aggregated expression itself
Sort result sets in outer queries instead of inside aggregates
Avoid porting DISTINCT+ORDER BY idioms from other engines blindly
Add lint checks comparing agg args and agg order-by keys

When it happens

Trigger: Writing e.g. array_agg(DISTINCT a ORDER BY b) or sum(DISTINCT x ORDER BY y): a sortKey expression is not contained in node.getArguments() nor resolved to the same column references as the arguments.

Common situations: Wanting DISTINCT values but sorted by a different column; porting from engines allowing this; hand-written ORDER BY on a formatted column rather than the aggregated expression.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/55d050ea23a6e7f5. Report an issue: GitHub.