---
title: "What is the purpose of the ROWS BETWEEN clause in SQL window functions?"  
description: "What is the purpose of the ROWS BETWEEN clause in SQL window functions?"  
author: "Sandra Emily"  
published: 2023-09-04  
updated: 2023-09-25  
canonical: https://www.mindstick.com/forum/159833/what-is-the-purpose-of-the-rows-between-clause-in-sql-window-functions  
category: "mssql server"  
tags: ["sql server", "sql"]  
reading_time: 2 minutes  

---

# What is the purpose of the ROWS BETWEEN clause in SQL window functions?

What is the [purpose](https://yourviews.mindstick.com/view/247/no-fail-policy-failing-its-purpose) of the ROWS BETWEEN [clause in SQL](https://www.mindstick.com/forum/33663/use-of-group-by-and-having-clause-in-sql-server) [window functions](https://www.mindstick.com/forum/160191/explain-the-purpose-and-benefits-of-using-window-functions-with-examples)?

## Replies

### Reply by Aryan Kumar

The "ROWS BETWEEN" [clause](https://www.mindstick.com/forum/33937/difference-between-throw-exception-and-throw-clause) in [SQL window](https://www.mindstick.com/forum/159829/provide-examples-of-common-sql-window-functions-and-their-use-cases) [functions](https://www.mindstick.com/forum/160140/explain-the-role-of-functions-as-a-service-faas-in-serverless-computing) is used to define a window or a subset of rows within the result set over which the window function operates. It allows you to specify the range or frame of rows that should be considered when performing calculations with window functions.

The "ROWS BETWEEN" clause typically includes the following options:

1. **UNBOUNDED PRECEDING**: This option includes all rows from the start of the partition up to and including the current row.
2. **n PRECEDING**: Here, 'n' represents a specific number of rows preceding the current row, including the current row itself. For example, if you specify "2 PRECEDING," it includes the current row and the two rows before it.
3. **CURRENT ROW**: This refers to the current row being processed by the window function.
4. **n FOLLOWING**: Similar to "n PRECEDING," this option includes the current row and 'n' rows following the current row.
5. **UNBOUNDED FOLLOWING**: It includes all rows from the current row up to the end of the partition.

The "ROWS BETWEEN" clause is particularly useful for performing calculations that involve a specific range of rows within a partition. For example, you can calculate rolling averages, cumulative sums, or other aggregates over a defined window of rows.

Here's an example of how you might use the "ROWS BETWEEN" clause in SQL:

```plaintext
SELECT
    OrderDate,
    OrderAmount,
    SUM(OrderAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS RollingSum
FROM
    Orders
```

In this example, the "ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING" clause specifies a window that includes the current row and the row immediately before and after it. This allows you to calculate a rolling sum of order amounts for each customer within this specific window.


---

Original Source: https://www.mindstick.com/forum/159833/what-is-the-purpose-of-the-rows-between-clause-in-sql-window-functions

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.