E121-03, “Value expressions in ORDER BY clause”

1. Some variants

Variants

This mandatory feature allows order by keys to be expression rather than just column references. Those expressions need to have a column reference nevertheless (constant expressions are not allowed anymore). Thus there are two variants to consider: ⓵ references to selected columns by name; ⓶ references to columns produced in the from clause, if the query is simple.0

Related

• Articles

  • Order by Has Come a Long Way

• Product Documentation

  • PostgreSQL

  • SQL Server

• Standard Features

  • Mandatory Features

    • E121-01, “DECLARE CURSOR”

      • E121-02, “ORDER BY columns need not be in select list”

  • Optional Features

    • F850, “Top-level <order by clause> in <query expression>”

    • F851, “ORDER BY in subqueries”

    • F855, “Nested ORDER BY in query expression”

    • T611, “Elementary OLAP operations”

      • order by … nulls (first|last)

Normative references

E121-03, “Value expressions in ORDER BY clause” is an mandatory feature in ISO/IEC 9075-2:2023. It first appeared in the 1999 edition. Previously (SQL-92), expressions were not allowed in order by.

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Bluesky or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »