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

Instruction

Excellent! If our window frame has CURRENT ROW as one of the boundaries, SQL Server allows us to use 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 n FOLLOWING means BETWEEN CURRENT ROW AND n FOLLOWING
  • ROWS CURRENT ROW means BETWEEN CURRENT ROW AND CURRENT ROW

As a way of example, the following query:

SELECT
  Id,
  Name,
  IntroducedDate,
  COUNT(Id) OVER(ORDER BY IntroducedDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CountId
FROM Product;

Can be rewritten to:

SELECT
  Id,
  Name,
  IntroducedDate,
  COUNT(Id) OVER(ORDER BY IntroducedDate ASC ROWS UNBOUNDED PRECEDING) AS CountId
FROM Product;

Exercise

You will now have a chance to practice abbreviations. Pick those stock changes which refer to ProductId = 3. For each of them, show the Id, ChangedDate, Quantity, and the running total, indicating the current stock status. Sort the rows by the ChangedDate in ascending order. Name the last column SumQuantity.

Stuck? Here's a hint!

Use the abbreviated form: ROWS UNBOUNDED PRECEDING.