{"record":{"id":"59be0d7b163065a8","repo":"prestodb/presto","slug":"order-by-must-be-in-select","errorCode":"ORDER_BY_MUST_BE_IN_SELECT","errorMessage":"For SELECT DISTINCT, ORDER BY expressions must appear in select list","messagePattern":"For SELECT DISTINCT, ORDER BY expressions must appear in select list","errorType":"validation","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java","lineNumber":5516,"sourceCode":"            if (rowCount < 0) {\n                throw new SemanticException(INVALID_OFFSET_ROW_COUNT, node, \"OFFSET row count must be greater or equal to 0 (actual value: %s)\", rowCount);\n            }\n            analysis.setOffset(node, rowCount);\n        }\n\n        private void verifySelectDistinct(QuerySpecification node, List<Expression> outputExpressions)\n        {\n            for (SortItem item : node.getOrderBy().get().getSortItems()) {\n                Expression expression = item.getSortKey();\n\n                if (expression instanceof LongLiteral) {\n                    continue;\n                }\n\n                Expression rewrittenOrderByExpression = ExpressionTreeRewriter.rewriteWith(new OrderByExpressionRewriter(extractNamedOutputExpressions(node.getSelect())), expression);\n                int index = outputExpressions.indexOf(rewrittenOrderByExpression);\n                if (index == -1) {\n                    throw new SemanticException(ORDER_BY_MUST_BE_IN_SELECT, node.getSelect(), \"For SELECT DISTINCT, ORDER BY expressions must appear in select list\");\n                }\n\n                if (!isDeterministic(expression)) {\n                    throw new SemanticException(NONDETERMINISTIC_ORDER_BY_EXPRESSION_WITH_SELECT_DISTINCT, expression, \"Non deterministic ORDER BY expression is not supported with SELECT DISTINCT\");\n                }\n            }\n        }\n\n        private List<Expression> analyzeOrderBy(Node node, List<SortItem> sortItems, Scope orderByScope)\n        {\n            ImmutableList.Builder<Expression> orderByFieldsBuilder = ImmutableList.builder();\n\n            for (SortItem item : sortItems) {\n                Expression expression = item.getSortKey();\n\n                if (expression instanceof LongLiteral) {\n                    // this is an ordinal in the output tuple\n","sourceCodeStart":5498,"sourceCodeEnd":5534,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java#L5498-L5534","documentation":"With SELECT DISTINCT, every ORDER BY expression must appear in the SELECT list, because ordering by a value not present in the deduplicated output is ill-defined. Analysis rewrites each ORDER BY expression against output names and fails if it is not found.","triggerScenarios":"SELECT DISTINCT a FROM t ORDER BY b; or ORDER BY expression like ORDER BY a+1 when only a is selected.","commonSituations":"Adding DISTINCT to an existing query with pre-existing ORDER BY; users sorting by a hidden column; ORM-generated queries mixing distinct with sort keys.","solutions":["Add the ORDER BY expression to the SELECT list","Order by an aliased output column (ORDER BY alias)","Drop DISTINCT if full-row dedup with external sort is intended (restructure query)","Wrap in an outer query: inner SELECT DISTINCT, outer ORDER BY"],"exampleFix":"// before\nSELECT DISTINCT department FROM employees ORDER BY salary;\n// after\nSELECT DISTINCT department, salary FROM employees ORDER BY salary;","handlingStrategy":"validation","validationCode":"if (isDistinct && orderByExpressions.stream().noneMatch(selectList::contains)) {\n    throw new IllegalArgumentException(\"ORDER BY expressions must appear in SELECT list for DISTINCT\");\n}","typeGuard":null,"tryCatchPattern":"try { runQuery(); } catch (SemanticException e) { if (e.getCode() == ORDER_BY_MUST_BE_IN_SELECT.toErrorCode()) { addSortKeyToSelectAndRetry(); } throw e; }","preventionTips":["When adding DISTINCT, audit existing ORDER BY clauses","Order by select-list aliases only","Use outer-query ordering when dedup + external sort is needed"],"tags":["distinct","order-by","sql-semantics"],"backgroundTag":"order-by-not-in-select","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"}