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.
Sub clauses:
Product specific: over(range between…)
(strings, multi-columns)
The over
clause, including the nested order by
, is defined in ISO/IEC 9075:2016-2 as part of optional feature T611, “Elementary OLAP operations”.
The essence of SQL tuning in 200 pages
Buy on Amazon
(paperback only)
Paperback and PDF also available at Markus’ store.
Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »