{"record":{"id":"55d050ea23a6e7f5","repo":"prestodb/presto","slug":"order-by-must-be-in-aggregate","errorCode":"ORDER_BY_MUST_BE_IN_AGGREGATE","errorMessage":"For aggregate function with DISTINCT, ORDER BY expressions must appear in arguments","messagePattern":"For aggregate function with DISTINCT, ORDER BY expressions must appear in arguments","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java","lineNumber":425,"sourceCode":"                                node.getName(),\n                                windowFunctions);\n                    }\n\n                    if (node.getOrderBy().isPresent()) {\n                        List<Expression> sortKeys = node.getOrderBy().get().getSortItems().stream()\n                                .map(SortItem::getSortKey)\n                                .collect(toImmutableList());\n                        if (node.isDistinct()) {\n                            List<FieldId> fieldIds = node.getArguments().stream()\n                                    .map(NodeRef::of)\n                                    .map(columnReferences::get)\n                                    .filter(Objects::nonNull)\n                                    .flatMap(Collection::stream)\n                                    .collect(toImmutableList());\n                            for (Expression sortKey : sortKeys) {\n                                if (!node.getArguments().contains(sortKey)\n                                        && !(columnReferences.containsKey(NodeRef.of(sortKey)) && fieldIds.containsAll(columnReferences.get(NodeRef.of(sortKey))))) {\n                                    throw new SemanticException(\n                                            ORDER_BY_MUST_BE_IN_AGGREGATE,\n                                            sortKey,\n                                            \"For aggregate function with DISTINCT, ORDER BY expressions must appear in arguments\");\n                                }\n                            }\n                        }\n                        // ensure that no output fields are referenced from ORDER BY clause\n                        if (orderByScope.isPresent()) {\n                            for (Expression sortKey : sortKeys) {\n                                verifyNoOrderByReferencesToOutputColumns(\n                                        sortKey,\n                                        REFERENCE_TO_OUTPUT_ATTRIBUTE_WITHIN_ORDER_BY_AGGREGATION,\n                                        \"ORDER BY clause in aggregation function must not reference query output columns\");\n                            }\n                        }\n                    }\n\n                    // ensure that no output fields are referenced from ORDER BY clause","sourceCodeStart":407,"sourceCodeEnd":443,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java#L407-L443","documentation":"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).","triggerScenarios":"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.","commonSituations":"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.","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))"],"exampleFix":"// before\nSELECT array_agg(DISTINCT customer ORDER BY order_date) FROM orders GROUP BY region\n// after\nSELECT array_agg(DISTINCT customer ORDER BY customer) FROM orders GROUP BY region","handlingStrategy":"validation","validationCode":"// ensure DISTINCT aggregate ORDER BY keys are subsets of arguments\nList<Expression> args = List.of(new Identifier(\"customer\"));\nList<Expression> sortKeys = List.of(new Identifier(\"order_date\"));\nif (!args.containsAll(sortKeys)) {\n    throw new IllegalArgumentException(\"ORDER BY keys must appear in DISTINCT aggregate arguments\");\n}","typeGuard":null,"tryCatchPattern":"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; }","preventionTips":["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"],"tags":["sql","aggregation","distinct","order-by","presto"],"backgroundTag":"distinct-order-by-mismatch","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}