Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
6. Defining the window frame
Default window frame


Okay. Let's jump into the brackets of OVER(...) and discuss the details. We'll start with ROWS, because it is a bit easier to explain than RANGE. The general syntax is as follows:

ROWS BETWEEN LowerBound AND UpperBound

You know BETWEEN already – it's used to define a range. So far, you've used it to define a range of values – this time, we're going to use it to define a range of rows instead. What are the two bounds? The bounds can be any of the five options:

  • UNBOUNDED PRECEDING – the first possible row.
  • n PRECEDING – the n-th row before the current row (instead of n, write the number of your choice).
  • CURRENT ROW – the current row only.
  • n FOLLOWING – the n-th row after the current row.
  • UNBOUNDED FOLLOWING – the last possible row.

The lower bound must come before the upper bound. In other words, a construction like: ...ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING doesn't make sense. You'll get an error from SQL Server if you run it.


Take a look at the example on the right. The query computes:

  • the total price of all orders placed so far (this kind of sum is called a running total), and
  • the total price of the current order, the 3 preceding orders and the 3 following orders.