🧠 More Window Frame Options

So far, Xorthax has used window frames to look at rows behind the current row. But what if he wants to look further back—or even ahead?

There are a few more options that give you more control over your window.

🚀 Expanding the Frame

Window frames can be defined using additional keywords:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

These let you extend the frame to the beginning or end of the dataset.

• UNBOUNDED PRECEDING → start from the first row
• UNBOUNDED FOLLOWING → include all future rows

📊 Example: Looking Forward

🧾 Sample Data

SELECT OrderDate, Amount,
       SUM(Amount) OVER (
         ORDER BY OrderDate
         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
       ) AS FutureTotal
FROM Orders;

Result:

Each row now includes values from itself and all rows after it.

🧠 What’s Happening Here?

ORDER BY OrderDate defines the sequence.

The frame starts at the current row and extends forward to the end.

That means each row includes its own value plus all future values.

This is the opposite of a running total, which looks backward.

🔍 ROWS vs RANGE

So far, you’ve used ROWS to define frames.

ROWS counts a fixed number of rows relative to the current row.

RANGE works differently—it groups rows with the same value in the ORDER BY column.

🧾 Sample Data

Using ROWS

SELECT OrderDate, Amount,
       SUM(Amount) OVER (
         ORDER BY OrderDate
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS Total
FROM Orders;

Result:

Using RANGE

SELECT OrderDate, Amount,
       SUM(Amount) OVER (
         ORDER BY OrderDate
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS Total
FROM Orders;

Result:

With RANGE, both rows with the same date are included together.

With ROWS, each row is handled one at a time.

In most beginner cases, ROWS is easier to understand and gives more predictable results.

🧪 Practice What You Just Learned

SELECT orderid, orderdate, totalamount, SUM(totalamount) OVER (ORDER BY orderdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_total FROM orders ORDER BY orderdate;

➡️ Next Step

Now that you understand how frames define groups and sequences, you can start comparing rows directly.

Next, you’ll learn how to look at previous and next rows.

Want to go deeper?