# Add **Author:** Hannu Krosing **Source:** Individual Expert Contribution **Status:** Discussion Paper **Date:** August 1, 2026 **Document:** ISO/IEC JTC 1/SC 32/WG 3:XXX-000 draft-01 --- ## Contents 1. [Abstract](#1-abstract) 2. [References](#2-references) 3. [Discussion](#3-discussion) 3.1. [The Problem](#31-the-problem) 3.2. [The Solution](#32-the-solution) 3.3. [Use of ORDER BY inside Parentheses](#33-use-of-order-by-inside-parentheses) 3.4. [Interaction with SELECT](#34-interaction-with-select) 3.5. [Interaction with UNION](#35-interaction-with-union) 3.6. [Execution Semantics (Buffering vs State Store)](#36-execution-semantics-buffering-vs-state-store) 4. [Syntax](#4-syntax) 5. [Design Rationale](#5-design-rationale) 5.1. [Why Inline ORDER BY inside Parentheses](#51-why-inline-order-by-inside-parentheses) 5.2. [Why Not Row Number Over Workaround](#52-why-not-row-number-over-workaround) 6. [Checklist](#6-checklist) --- ## 1. Abstract This paper proposes adding a `DISTINCT ON` clause to the SQL standard. The clause allows duplicate elimination based on a subset of columns (keys), rather than all columns as in standard `DISTINCT`. Crucially, it introduces the syntax `DISTINCT ON (keys [ ORDER BY sort_keys ])` where the `ORDER BY` is placed *inside* the parentheses and is **optional**. If `ORDER BY` is omitted, the preserved row is implementation-dependent (equivalent to selecting `ANY` row). This feature is proposed for both `` (SELECT) and `` (specifically UNION). ## 2. References * [Foundation] ISO/IEC 9075-2, SQL/Foundation (IWD) * [Standard SQL ORDER BY Usages] The following standard features already use `ORDER BY` inside localized clauses (outside the global query-level `ORDER BY`): * **Window Functions (`OVER` clause):** `OVER (PARTITION BY ... ORDER BY ...)` (SQL:1999). * **Ordered-Set Aggregates:** `WITHIN GROUP (ORDER BY ...)` (SQL:2003, e.g., `PERCENTILE_CONT`). * **Aggregate Functions (inline):** `LISTAGG(val ORDER BY ...)` (SQL:2016), `ARRAY_AGG(val ORDER BY ...)` (often supported, SQL:2016). * **XML/JSON Aggregates:** `XMLAGG(val ORDER BY ...)` (SQL/XML:2006), `JSON_ARRAYAGG(val ORDER BY ...)` (SQL:2016). --- ## 3. Discussion ### 3.1. The Problem Standard SQL only supports `DISTINCT` which operates on all columns of the result set. Often, users need to fold duplicates based on a subset of columns (e.g., "get the latest status for each user"). Currently, users must use window functions (like `ROW_NUMBER()`) or complex correlated subqueries to achieve this. Example workaround using window functions: ```sql SELECT user_id, status, updated_at FROM ( SELECT user_id, status, updated_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) as rn FROM user_statuses ) t WHERE rn = 1; ``` Drawbacks of workarounds: 1. **Verbosity:** Requires a subquery and window function. 2. **Optimizer Complexity:** Optimizers often fail to optimize this well. 3. **Set Operations:** There is no clean way to apply this behavior *during* set operations like `UNION`. Merging two streams of data and keeping the newest entry per key requires applying the workaround after the union, which prevents early pruning and efficient merge-joins. ### 3.2. The Solution We propose adding `ON (keys [ ORDER BY sort_keys ])` as an optional modifier to `DISTINCT`. General Syntax: ```sql DISTINCT [ ON (distinct_keys [ ORDER BY sort_keys [ASC|DESC] ]) ] ``` For `SELECT`: ```sql SELECT DISTINCT ON (user_id ORDER BY updated_at DESC) user_id, status, updated_at FROM user_statuses; ``` For `UNION`: ```sql SELECT user_id, status, updated_at FROM active_users UNION DISTINCT ON (user_id ORDER BY updated_at DESC) SELECT user_id, status, updated_at FROM archived_users; ``` If the `ORDER BY` sub-clause is omitted, the row preserved for each distinct key is implementation-dependent (equivalent to selecting `ANY` row). ```sql -- Returns user_id and an arbitrary status/updated_at for each user SELECT DISTINCT ON (user_id) user_id, status, updated_at FROM user_statuses; ``` ### 3.3. Use of ORDER BY inside Parentheses By placing the `ORDER BY` *inside* the `DISTINCT ON` parentheses, we follow the precedent set by window functions and ordered-set aggregates. This syntactically binds the ordering to the duplicate elimination logic, keeping it independent of the query's final output ordering. ### 3.4. Interaction with SELECT The inline `ORDER BY` in `DISTINCT ON` defines the "selection sort". The query may still have a global `ORDER BY` which defines the "presentation sort". ```sql SELECT DISTINCT ON (user_id ORDER BY updated_at DESC) user_id, status, updated_at FROM user_statuses ORDER BY status ASC; ``` Here, we keep the latest status per user, but the final output is sorted by `status` ascending. The global `ORDER BY` is completely decoupled from the distinct keys. ### 3.5. Interaction with UNION `UNION DISTINCT ON` merges two relations and key-duplicates them. ```sql query_expression_1 UNION DISTINCT ON (key_cols [ORDER BY sort_cols]) query_expression_2 ``` * **Without ORDER BY:** Equivalent to standard `UNION` but only comparing `key_cols`. The executor can return rows as soon as they are seen (using a hash table of keys). If a key is already in the hash table, subsequent rows with that key are discarded (arbitrary row selection). * **With ORDER BY:** Requires comparing the `sort_cols` to decide which row to keep. ### 3.6. Execution Semantics (Buffering vs State Store) When `ORDER BY` is specified in `UNION DISTINCT ON`, the executor must determine which row to keep. We define two semantic models for execution, especially relevant for recursive queries (CTEs): 1. **Buffering (Blocking) Semantics:** The set operation buffers all input from both sides, groups by `key_cols`, sorts each group by `sort_cols`, and emits the first row of each group. This guarantees that the output contains *only* the distinct rows with the best sort values. * *Drawback:* Blocking operation; cannot return rows incrementally. 2. **State Store Semantics (Non-blocking / Incremental):** The operator maintains a state store (hash table) of `key_cols -> best_sort_values`. * When a row is processed: * If the key is not in the store, store it and emit it. * If the key is in the store, compare `sort_cols`. If the new row is "better", update the store and *emit the new row*. * *Result:* The final output stream may contain duplicates (an older/worse row emitted first, then a newer/better row emitted later). However, the *state* is kept minimal. * For Recursive CTEs, this allows pruning: we only propagate the "better" path to the next iteration. --- ## 4. Syntax We modify `` and `` in SQL/Foundation. ```bnf ::= | ::= | UNION [ | ] | EXCEPT [ | ] ::= DISTINCT ON [ ] ::= ::= ORDER BY ::= SELECT [ | ]