Difference Between ROWS and RANGE in Window Functions

Are you familiar with ROWS and RANGE window functions but not sure how they are different? This article makes those differences clear.

In using SQL window functions, you’ve probably come across ROWS and RANGE clauses. If you haven’t, they are both parts of the Window Functions course. If you have, it’s quite likely you have been left confused why there are two keywords for the same function.

Well, they are not the same! The truth is, sometimes they do return the same result. However, there are situations when they give you completely different results.

It’s important to know ROWS and RANGE are different in SQL. You need to know when to use which one.

ROWS and RANGE: A Brief Overview

Both ROWS and RANGE clauses in SQL limit the rows considered by the window function within a partition.

The ROWS clause does that quite literally. It specifies a fixed number of rows that precede or follow the current row regardless of their value. These rows are used in the window function.

On the other hand, the RANGE clause logically limits the rows. That means it considers the rows based on their value compared to the current row.

Let’s see a practical example of how these two clauses can return different results.

I’m sure you’re familiar with window functions and their syntax. If you need to refresh your memory a little bit, use this cheat sheet to make sure you can follow the rest of the article. If you need to repeat some basics or any other topic, there are more cheat sheets.

Difference Between ROWS and RANGE: A Practical Example

To show you how ROWS and RANGE work differently, I’ll use the table revenue_consolidation. Here’s the data it contains:

As you can see, it contains revenue data for two shops of one company for the second quarter of 2021. This table contains some duplicates. For instance, Shop 2 has two rows with different values for the 04/2021 revenue. There are other duplicates; you can find them in the table. This is because sometimes, accounting adjustments can change the total revenue after the books are closed for the month.

Now that we’re familiar with the data, let’s see how ROWS and RANGE behave. Let’s try to calculate the cumulative revenue sum for every shop. I’ll do that first using ROWS, then using RANGE.

How ROWS Works

The ROWS clause in SQL defines a window frame as the number of rows preceding and/or following the current row.

SELECT
  period,
  shop,
  revenue,
  SUM(revenue) OVER(
    PARTITION BY shop
    ORDER BY period ASC
    ROWS UNBOUNDED PRECEDING
  ) AS rows_cumulative_revenue
FROM revenue_consolidation;

To make it clearer, let me show you what the manual calculation looks like. The principle is: revenue (for the current row) + rows_cumulative_revenue (from the previous row) = rows_cumulative_revenue (for the current row). So, for the first row, it is 154,552.82 + 0 (because there’s no previous Shop 1 row) = 154,552.82. This is exactly what you see in the table above.

Let’s move to the second row with the same principle in mind: 1,489,774.16 + 154,552.82 = 1,644,326.98. The next row is: 1,845,662.35 + 1,644,326.98 = 3,489,989.33. You do that for all the Shop 1 rows.

When you come to the Shop 2 rows, you start all over again: 341,227.53 + 0 (because there’s no previous Shop 2 row) = 341,227.53 and so on until you reach the end of the table.

How RANGE Works

The RANGE clause in SQL defines the window frame by the number of rows preceding and/or following the current row plus all other rows that have the same value.

SELECT
  period,
  shop,
  revenue,
  SUM(revenue) OVER(
    PARTITION BY shop
    ORDER BY period ASC
    RANGE UNBOUNDED PRECEDING
  ) AS range_cumulative_revenue
FROM revenue_consolidation;

As you can see in the above table, the RANGE clause uses all the previous rows, the current row, and another row that contains the revenue for Shop 1 and 2021/06.

The manual calculation would look like in the previous example, but with a twist. The principle is the same for the first two rows: revenue (for the current row) + rows_cumulative_revenue (for the previous row) = rows_cumulative_revenue (for the current row). That is, 154,552.82 + 0 = 154,552.82 for the first row and 1,489,774.16 + 154,552.82 = 1,644,326.98 for the second row.

Now comes the twist! The next two rows both contain revenue for 2021/06 at Shop 1, and they will be treated together by adding them up. That way, there will be only one value for the cumulative revenue for the month. This matches how it works in the real world: there is only one cumulative value at the end of the month. The principle is: revenue (for the current row) + revenue (for all the other rows for 2021/06 at Shop 1) + rows_cumulative_revenue (for the previous row) = rows_cumulative_revenue (for the current row). That is, 1,845,662.35 + 52,489.35 + 1,644,326.98 = 3,542,478.68. This is the cumulative revenue for Shop 1 for the period 2021/06, and the same value appears in both rows; in other words, the cumulative revenue is obtained and then the same value is replicated for all rows of the same shop and month.

Want to Practice Window Functions?

The purpose of this short article was to show you the difference between the ROWS and RANGE clauses. I did it as explicitly as possible, without beating around the bush. If that’s not enough for you and you want to practice some more, check out this course.