Introduction
ROWS
RANGE
15. Setting RANGE boundaries
Default window frame
Summary

Instruction

Great! The window frame of RANGE is defined just like the window frame of ROWS: use BETWEEN ... AND ..., or the abbreviated version.

You can use UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING, as well as CURRENT ROW, but you can't use n PRECEDING and n FOLLOWING with RANGE.

Why not? With ROWS, we always know there is a single current row, and so we can easily calculate the previous or next row(s). With RANGE, the database must understand what "three preceding values" means. It's easy to understand "three preceding days" but what are the "three numbers preceding 14.5"? The SQL standard defined the meaning of n PRECEDING and n FOLLOWING for RANGE, but SQL Server did not implement it.

Exercise

For each StockChange with ProductId = 7, show its Id, Quantity, ChangedDate and another column (name it StockChangeNumber) which will count the number of stock changes with ProductId = 7 on that particular date.

Stuck? Here's a hint!

Use the abbreviated version: RANGE CURRENT ROW.