SQL – ROWS BETWEEN

ROWS BETWEEN is used to calculating the running measure / aggregation in Analytical Functions. Whenever you use window functions you specify the rows which shall be considered for calculating measures. If you don’t specify anything then by default all the rows in the partition participate in calculating measures.

Some common aggregation function SUM() , MIN(),MAX() , COUNT() , AVG() .

Note– if you don’t specify anything in over() clause for partitioning, then by default all rows in dataset consider as an single partition .

 RANGE must always be from start to end i.e. Start must be before End.

Some Common Specifications

1. UNBOUNDED PRECEDING: All rows before current row are considered.
2. UNBOUNDED FOLLOWING: All rows after the current row are considered.
3. CURRENT ROW: Range starts or ends at CURRENT ROW.

You can also specify NUMERICAL values in place of “UNBOUNDED” keyword in above mentioned specifications like 1 PRECEDING and 2 FOLLOWING. Default value is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING i.e. all rows are considered.

Syntax for ROWS BETWEEN SQL

SELECT column1, function_name (column2) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM table_name .

Parameters	Description
rows between Unbounded Preceding and Current Row	includes the current row and all rows before current row
rows between N Preceding and Current Row	includes the current row and a specified number of rows before current row
rows between current and current row	includes only the current row
rows between N Preceding and M Following	includes specified rows before and after the current row
rows between Current and M Following	includes the current row and a specified number of rows after current row
rows between Current Row and Unbounded Following	includes the current row and all rows after current row

Steps To Create ROWS BETWEEN in SQL

Step 1: First we create and insert a data into a table.

CREATE TABLE department (
id integer ,
name varchar(20) ,
salary integer, profession varchar(20))
INSERT INTO department VALUES (1, 'Sakshi', 48000, 'Doctor');
INSERT INTO department VALUES (2,  'Rutik',10000, 'Data Engineer');
INSERT INTO department VALUES (3, 'Yash', 55000,'Loco Pilot');
INSERT INTO department VALUES (4,  'Aman', 46000,'Doctor');
INSERT INTO department VALUES (5,  'Amol', 62000, 'BACKEND DEVELOPERS');

Output:

Step 2: Let’s consider we want to calculate the total amount of salary till the current employee . Here we are going to see how to get the running salary total for each employee .

Query:

  SELECT  * , sum(SALARY) OVER (ROWS BETWEEN 
 UNBOUNDED PRECEDING  AND CURRENT ROW  ) 
 as RUNNING_SUM   FROM department 

Output:

Step 3: Calculating the the 3SUM for each employee . (3sum = current row + 1 Preceding + 1 Following ) .

Query:

 SELECT * , sum(SALARY) OVER (ROWS BETWEEN  1 PRECEDING
  AND 1 FOLLOWING)  as Runnning_sum FROM department  . 

Output: