Hi Vik
Finally had time to put this SQL Standard Propoasl together.
Please take a quick look and tell me what is missing or wrong and what
the next steps should be.
For example, should I mention somewhere that PostgreSQL supports a
partial version of the SELECT DISTINCT ON () ?
Regarding PGQ in PostgreSQL, this should allow a plain SQL target for
arbitrary length graph queries and at least some types of path finding
queries.
I hope to have some working PoC code for PostgreSQL within the next
few days so I can share actual working versions of both sample
recursive graph queries and the "overlay of read-only table" queries I
talked to you about.
-----
Hannu
# Add <distinct on clause>
**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 `<query specification>` (SELECT) and `<query expression>` (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 `<query expression body>` and `<query specification>` in SQL/Foundation.
```bnf
<query expression body> ::=
<non-join query expression>
| <join query expression>
<non-join query expression> ::=
<non-join query term>
| <query expression body> UNION [ <set quantifier> | <distinct on clause> ] <query term>
| <query expression body> EXCEPT [ <set quantifier> | <distinct on clause> ] <query term>
<distinct on clause> ::=
DISTINCT ON <left paren> <distinct key list> [ <distinct sort clause> ] <right paren>
<distinct key list> ::=
<value expression list>
<distinct sort clause> ::=
ORDER BY <sort specification list>
<query specification> ::=
SELECT [ <set quantifier> | <distinct on clause> ] <select list> <table expression>
```
---
## 5. Design Rationale
### 5.1. Why Inline ORDER BY inside Parentheses
Placing `ORDER BY` inside `DISTINCT ON (...)` decouples the selection logic from the presentation logic. It avoids PostgreSQL's current limitation where the global `ORDER BY` must have the distinct keys as prefix. It is also syntactically consistent with standard SQL window functions (`OVER (PARTITION BY ... ORDER BY ...)`) and aggregate ordering (`LISTAGG(... ORDER BY ...)`).
### 5.2. Why Not Row Number Over Workaround
While `ROW_NUMBER()` works, it is syntax-heavy and hard for optimizers to recognize as a simple "keep first" operation. A first-class `DISTINCT ON` syntax allows the planner to immediately choose optimal strategies like Index Scan (ordered) or HashAgg with replacement.
---
## 6. Checklist
[Standard WG3 Checklist - to be completed]
| Item | Status |
| --- | --- |
| Concepts. | TODO |
| Reserved and non-reserved keywords. | `DISTINCT`, `ON`, `ORDER` are already keywords. No new keywords needed. |
