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 |
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 |
Ranking ≤ 5
Exercise
Click to continue.



