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
RANGE
Default window frame
19. Default window frame – without ORDER BY
Summary

Instruction

Perfect! You may wonder what the default window frame is when it's not explicitly specified. In SQL Server, the following rules are used:

  • If you don't specify an ORDER BY clause within OVER(...), the whole partition of rows will be used as the window frame.
  • If you do specify an ORDER BY clause within OVER(...), the database will assume RANGE UNBOUNDED PRECEDING as the window frame.

Let's check both of these cases with some exercises.

Exercise

We'll start with not specifying an ORDER BY clause within OVER(...).

For each single order, show its Id, the date when it was placed, the total price, and the sum of all total prices (SumTotalPrice).

Notice that SUM() computes the sum of all prices in the table, even though you did not specify the window frame.