SQL2003’s Window Syntax

SQL2003 specifies the following syntax for window functions:

FUNCTION_NAME(expr) OVER {window_name|(window_specification)}

window_specification ::= [window_name][partitioning][ordering][framing]

partitioning ::= PARTITION BY value [, value...] [COLLATE collation_name]

ordering ::= ORDER [SIBLINGS] BY rule [, rule...]

rule ::= {value|position|alias} [ASC|DESC] [NULLS {FIRST|LAST}]

framing ::= {ROWS|RANGE} {start|between} [exclusion]

start ::= {UNBOUNDED PRECEDING|unsigned-integer PRECEDING|CURRENT ROW}

between ::= BETWEEN bound AND bound

bound ::= {start|UNBOUNDED FOLLOWING|unsigned-integer FOLLOWING}

exclusion ::= {EXCLUDE CURRENT ROW|EXCLUDE GROUP
              |EXCLUDE TIES|EXCLUDE NO OTHERS}

Get SQL in a Nutshell, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.