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
11. Abbreviations
RANGE
Default window frame
Summary

Instruction

Fine! If our window frame has CURRENT ROW as one of the boundaries, we can also use some abbreviated syntax to make things easier:

  • ROWS UNBOUNDED PRECEDING means BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • ROWS n PRECEDING means BETWEEN n PRECEDING AND CURRENT ROW
  • ROWS CURRENT ROW means BETWEEN CURRENT ROW AND CURRENT ROW

Note: these rules don't apply for FOLLOWING, only for PRECEDING and CURRENT ROW. As a way of example, the query:

SELECT
  id,
  name,
  introduced,
  COUNT(id) OVER(ORDER BY introduced ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM product;

Can be rewritten to:

SELECT
  id,
  name,
  introduced,
  COUNT(id) OVER(ORDER BY introduced ROWS UNBOUNDED PRECEDING)
FROM product;

Exercise

You will now have a chance to practice abbreviations. Pick those stock changes which refer to product_id = 3. For each of them, show the id, changed date, quantity, and the running total, indicating the current stock status. Sort the rows by the changed date in the ascending order.

Stuck? Here's a hint!

Use the abbreviated form: ROWS UNBOUNDED PRECEDING.