Row Vs. Range SQL Window Functions: Top Differences

ROW and RANGE are SQL window functions. These look similar but not the same. They both operate within the context of a window defined by an OVER clause in SQL queries, but they differ in how they define that window and how they calculate the result.

SQL Window Function: ROW

• The ROW window function operates on a physical count of rows. It considers the number of rows within the window without regard to the actual values of those rows.
• It doesn’t consider the values of the rows, only the count of rows.
• Examples of ROW window functions include ROW_NUMBER(), RANK(), and NTILE().

Example

-- ROW window function
SELECT
  id,
  value,
  ROW_NUMBER() OVER (ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_number
FROM
  your_table;

In this query, using ROW_NUMBER(), the window function assigns a unique sequential integer to each row based solely on the order of the rows.

SQL Window Function: RANGE

• The RANGE window function operates on the actual values within the window rather than on the count of rows.
• It considers the values of the rows in the window rather than just their count.
• RANGE functions are often used with window functions that aggregate data, such as SUM(), AVG(), MIN(), and MAX().
• The window frame defined by RANGE is based on the values in the ORDER BY clause.
• RANGE functions can be sensitive to ties (rows with the same value), unlike ROW functions.
• Not all database systems support RANGE window functions.

Example

-- RANGE window function
SELECT
  id,
  value,
  SUM(value) OVER (ORDER BY value RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM
  your_table;

In this query, using SUM() with a RANGE frame, the SQL window function calculates a running total of the ‘value’ column. The RANGE frame specifies that the window includes all rows from the beginning of the partition (UNBOUNDED PRECEDING) up to the current row.

Conclusion

So, while both ROW and RANGE SQL window functions operate within the context of a window, they differ in how they define that window and how they calculate results.