# ROWS clause in the Query [Resolved]

Posted by Sriharim under Sql Server on 8/27/2015 | Points: 10 | Views : 695 | Status : [Member] | Replies : 3
The following example uses the ROWS clause to define a window over which the rows are computed as the current row and the N number of rows that follow (1 row in this example).

`SELECT BusinessEntityID, TerritoryID     ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD    ,DATEPART(yy,ModifiedDate) AS SalesYear    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID                                              ORDER BY DATEPART(yy,ModifiedDate)                                              ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotalFROM Sales.SalesPersonWHERE TerritoryID IS NULL OR TerritoryID < 5;`

I didn't understand the use of/how the 'ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING' will work in above query ? Pls explain

---
Srihari

#### Responses

Posted by: Bandi on: 8/28/2015 [Member] [MVP] Platinum | Points: 50 0  The OVER clause will consider the current row, and a specific number of rows after the current row.`SELECT empid, salary, SUM(salary) over(order by empid ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) CurrentRowValuePlusNextRowValueFROM EMPLOYEEs` `Empid Salary CurrentRowValuePlusNextRowValue 2 5000.00 12000.00 3 7000.00 13000.004 6000.00 8500.005 2500.00 4000.006 1500.00 1500.00` 1st Row calculation: The Current Row + 1 next row means 5000 + 7000 = 120002nd Row calculation: The Current Row + 1 next row means 7000 + 6000 = 130003rd Row calculation: The Current Row + 1 next row means 6000 + 2500 = 85004th Row calculation: The Current Row + 1 next row means 2500 + 1500 = 40005th Row calculation: The Current Row + 1 next row means 1500 + No Next Row (i.e. 0) = 1500Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gifSriharim, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sriharim on: 9/2/2015 [Member] Starter | Points: 25 0 thanxs :-)Mark as Answer if its helpful to you --- SrihariSriharim, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Manub22 on: 9/22/2015 [Member] Starter | Points: 25 0 The option "ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING" that you specified with your OVER clause meant that the Query will calculate the moving SUM of the current row and the next row values.You can use and change lot of options here, like:- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING- ROWS BETWEEN CURRENT ROW AND 2 FOLLOWINGThis also works with RANGE option, like- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING... and many more. Check my blog post on the OVER clause and various options with example you can apply with this : http://sqlwithmanoj.com/2013/01/02/enhanced-over-clause-in-sql-server-2012-part2-2/ Sriharim, if this helps please login to Mark As Answer. | Alert Moderator
Latest Posts