- Some variants
This optional SQL feature covers the order by clause applied to a single leg of an union, intersect or except:
(SELECT *
FROM (VALUES (1), (2)) t(c)
ORDER BY c
FETCH FIRST 1 ROW ONLY
)
UNION ALL
SELECT *
FROM (VALUES (3)) t(c)
-- ORDER BY here applies to the result of the UNIONThe first leg of the union uses an order by clause. Note the such legs need to be put into parenthesis so that the scoping of the order by clause is unambiguous. Adding an order by clause to the last leg of union, intersect or except without parenthesis means to sort the total result.
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 this 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
Related
Articles
Order byHas Come a Long Way
Standard Features
Mandatory Features
Optional Features
Normative references
F855, “Nested ORDER BY in query expression” is an optional feature in ISO/IEC 9075-2:2023. It first appeared in the 2008 edition.
