Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in MS SQL Server

Introduction
ROWS
6. Defining the window frame
RANGE
Default window frame
Summary

Instruction

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.

Exercise

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.