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

1. Some variants

This optional SQL feature covers the fundamental functionality of the order by clause in queries:

SELECT c
  FROM (VALUES (1)
             , (2)
       ) t(c)
 ORDER BY c

Variants

The order by clause allows the following 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.

1. Expressions cannot contain select-names:
   

   select c AS x FROM (VALUES (1), (2)) t(c) ORDER BY x * 2

2. Also via bind parameter: order by ? • Negative parameters (but not literals) reverse the direction
3. Also via bind parameter: order by ?

Related

• Articles

  • Order by Has Come a Long Way

• Standard Features

  • Mandatory Features

    • E121-01, “DECLARE CURSOR”

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

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

  • Optional Features

    • F851, “ORDER BY in subqueries”

    • F855, “Nested ORDER BY in query expression”

    • T611, “Elementary OLAP operations”

      • order by … nulls (first|last)

Normative references

F850, “Top-level <order by clause>in <query expression>” is an optional feature in ISO/IEC 9075-2:2023. It first appeared in the 2008 edition.

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 »