The over
accepts the optional partition by
clause to limit the scope of the window function to those rows that have the “same” values in the listed expressions.
SELECT *, COUNT(*) OVER(PARTITION BY c1, c2)
FROM …
Returns the original result with an additional column stating how many rows the result has with the “same“ values in c1
and c2
as the current row.
In this context, “same” means is not distinct from
, which treats two null
values as indistinguishable (very much like group by
does).
Sub clauses:
Product specific: over(range between…)
(strings, multi-columns)
The over
clause, including partition by
, is defined in ISO/IEC 9075-2:2023 as part of optional feature T611, “Elementary OLAP operations”.
You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-sql.com on your radar.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »