- Some variants
- Only in combination with
fetch firstor the like - Optimized away if meaningless
This optional SQL feature covers the order by clause in subqueries:
SELECT c
FROM (SELECT *
FROM (VALUES (1), (2)) t(c)
ORDER BY c
FETCH FIRST 1 ROW ONLY
) t2Note that the only reason to use order by in subqueries is to assign meaning to the word “first” in the offset and fetch first clauses—and similar non-standard clauses like limit or top.
Variants
This feature allows the following order by keys types: ⓵ references to selected columns by name; ⓶ references to columns produced in the from clause if the query is simple0 and ⓷ expressions that contain at least one such column reference.1 Note that positional referencing by an unsigned integer value ⓸ is not standard SQL anymore. While the standard even allows nested order by clause in absences of offset and fetch first, some systems do not allow such pointless clause ⓹.
- Expressions cannot contain selected names:
select c AS x FROM (VALUES (1), (2)) t(c) ORDER BY x - Also via bind parameter:
order by ?• Negative parameters (but not literals) reverse the direction - Also via bind parameter:
order by ? - Reasonable; ignored in other charts
SQL Server does not allow using order by in subqueries without such clause. MariaDB actually optimizers them away unless they are required for limit or similar clauses—and this is allowed by the SQL standard as the row-order in subqueries is not preserved in the outer query.2
Related
Articles
Order byHas Come a Long Way
Standard Features
Mandatory Features
Optional Features
Normative references
F851, “ORDER BY in subqueries” is an optional feature in ISO/IEC 9075-2:2023. It first appeared in the 2008 edition.
