ROWS and RANGE are important SQL window functions. If you have been writing queries in SQL for a while, you may have experienced confusion regarding why there are two keywords for the same function, even when they give the same results in some situations. However, they are not the same! In this tutorial, we will learn how and when to use the ROWS and RANGE and how they differ.
ROWS and RANGE Clause
The ROWS clause positions the window based on the physical order of the rows in the partition. For example, specifying “ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING” includes the current row and the first three rows. This is useful when calculations rely on the exact locations of rows in a partition.
Whereas, the RANGE clause defines the window in logical units such as values or intervals made from the sequence column. For example, if you specify “RANGE BETWEEN INTERVAL ‘1’ DAY PRECEDING AND INTERVAL ‘1’ DAY FOLLOWING” includes rows whose values go one day before and after the current row value. It is especially useful when calculations are for column( s) rather than row positions based on the values of the system.
Example:
Let’s understand ROWS and RANGE with the help of the Info table.
Now, if we want to fetch the average salary and display it against every row of the Info table, we might think that the below query can do it:
SELECT Name, Gender, Salary,
AVG(Salary) OVER(ORDER BY Salary) AS Average
FROM Info;Code language: SQL (Structured Query Language) (sql)
As we can see from the above result set, the query doesn’t provide the overall salary average, it fetched the average of the current row and the row preceding the current row. It happened because the default value of RANGE and ROWS is applied which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
This may be fixed by giving the ROWS or RANGE clause an explicit value, as seen below. The window function is instructed to work on the set of rows starting from the first row in the partition to the last row in the partition by using the expression ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
SELECT Name, Gender, Salary,
AVG(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Average
FROM Info;Code language: SQL (Structured Query Language) (sql)
The following can also be achieved using “RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”.
SELECT Name, Gender, Salary,
AVG(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Average
FROM Info;Code language: SQL (Structured Query Language) (sql)
The following query can be used if you want to fetch the average salary:
- The current row
- Two row PRECEDING the current row
- Two row FOLLOWING the current row
SELECT Name, Gender, Salary,
AVG(Salary) OVER(ORDER BY Salary
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS Average
FROM Info;Code language: SQL (Structured Query Language) (sql)
Also Read: Using Partitions with Ranking Functions – SQL Window Functions Guide
Difference Between ROWS and RANGE in Window Functions
Let us understand their difference with the following Info table.
We need to calculate the running total and display it against every row of the Info table.
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Info;Code language: SQL (Structured Query Language) (sql)
It can also be achieved through “ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”.
Now, to understand the difference we need to add some duplicate values in the Salary column for the Info table.
Update Info set Salary = 1000 where Id = 2;
Update Info set Salary = 3000 where Id = 4;Code language: SQL (Structured Query Language) (sql)Now again we are going to fetch the Runningtotal, the only difference here is that we have duplicate values in the table now.
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Info;Code language: SQL (Structured Query Language) (sql)
Now, we will fetch the Runningtotal using the RANGE clause.
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Info;Code language: SQL (Structured Query Language) (sql)
Look at the above table, it is not the same RunningTotal as we expected. Here, the running total of the duplicate values is treated as a Single entity.
Therefore, the main difference between ROWS and RANGE is how they are treated when they have duplicate values. RANGE treats duplicate values as Single entities whereas ROWS treat them as distinct values.
Conclusion
In this tutorial, we have learned about the RANGE and ROWS clause in the Windows function with multiple examples, also we have seen how we can achieve the same result using multiple queries (given the situation of no duplicate values in the case of RANGE). We have learned about the differences between them. This is one of the most important topics of advanced SQL. We hope you find this article informative and enjoyable.