Introduction
ROWS
RANGE
14. ROWS and RANGE – what's the difference?
Default window frame
Summary

Instruction

The difference between ROWS and RANGE is similar to the difference between the ranking functions ROW_NUMBER() and RANK().

The query with ROWS sums the TotalPrice for all rows which have a ROW_NUMBER() less than or equal to the row number of the current row:

SELECT
  Id,
  PlacedDate,
  TotalPrice,
  ROW_NUMBER() OVER(ORDER BY PlacedDate ASC) AS RowNumber,
  SUM(TotalPrice) OVER(ORDER BY
    PlacedDate ASC ROWS UNBOUNDED PRECEDING) AS RunningSum
FROM SingleOrder;
Id PlacedDate TotalPrice RowNumber RunningSum
4 2016-06-13 2659.63 1 2659.63
5 2016-06-13 602.03 2 3261.66
6 2016-06-13 3599.83 3 6861.49
7 2016-06-29 4402.04 4 11263.53
1 2016-07-10 3876.76 5 15140.29
2 2016-07-10 3949.21 6 19089.50
3 2016-07-18 2199.46 7 21288.96
10 2016-08-01 4973.43 8 26262.39
11 2016-08-05 3252.83 9 29515.22
12 2016-08-05 3796.42 10 33311.64
8 2016-08-21 4553.89 11 37865.53
9 2016-08-30 3575.55 12 41441.08
sum of prices with RowNumber ≤ 6

The next query uses RANGE to sum up the TotalPrice for all rows which have a RANK() less than or equal to the rank of the current row.

SELECT
  Id,
  PlacedDate,
  TotalPrice,
  RANK() OVER(ORDER BY PlacedDate ASC) AS Ranking,
  SUM(TotalPrice) OVER(ORDER BY
    PlacedDate ASC RANGE UNBOUNDED PRECEDING) AS RunningSum
FROM SingleOrder;
Id PlacedDate TotalPrice Ranking RunningSum
4 2016-06-13 2659.63 1 6861.49
5 2016-06-13 602.03 1 6861.49
6 2016-06-13 3599.83 1 6861.49
7 2016-06-29 4402.04 4 11263.53
1 2016-07-10 3876.76 5 19089.50
2 2016-07-10 3949.21 5 19089.50
3 2016-07-18 2199.46 7 21288.96
10 2016-08-01 4973.43 8 26262.39
11 2016-08-05 3252.83 9 33311.64
12 2016-08-05 3796.42 9 33311.64
8 2016-08-21 4553.89 11 37865.53
9 2016-08-30 3575.55 12 41441.08
sum of prices with Ranking ≤ 5

Exercise

Click Next exercise to continue.