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

Introduction
ROWS
6. Window frame definition
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 they are a bit easier to explain than RANGE. The general syntax is as follows:

ROWS BETWEEN lower_bound AND upper_bound

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.
  • PRECEDING – the n-th row before the current row (instead of n, write the number of your choice).
  • CURRENT ROW – simply current row.
  • 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 and you'll get an error 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),
  • the total price of the current order, 3 preceding orders and 3 following orders.