over(order by …)

The over accepts the optional order by clause that can be used to establish an order for framing or ranking and navigational window functions.

SELECT *, COUNT(*) OVER(ORDER BY c1, c2)
  FROM …

Note that the order by in over activates the default framing range between unbounded preceding and current row. That means, the result of the query is actually different when the order by clause is removed.

Related

• Sub clauses:

  • over(partition by …)

  • Framing:

    • over(… rows between …)

    • over(… range between …) (numeric), over(… range between …) (datetime)

    • over(… groups between …)

    • Frame exclusion:

      • over(… exclude no others)

      • over(… exclude current row)

      • over(… exclude group)

      • over(… exclude ties)

• Product specific: over(range between…) (strings, multi-columns)

Normative References

The over clause, including the nested order by, is defined in ISO/IEC 9075-2:2023 as part of optional feature T611, “Elementary OLAP operations”.