Sub-Grouping by consecutive rows

Question

Sub-Grouping by consecutive rows

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

mishka-723908 SSCommitted Points: 1675 More actions · Answer 1

Hello,

Thank you very much for your help. I appreciate it. Will this have the CTE recursion limit? For now The data sets are small, but we may get larger files.

Thanks again for your help.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

mishka-723908 (1/24/2016)
Will this have the CTE recursion limit?

The solution Jeff posted uses CTE's, but no recursive CTE's. So no, it does not have a recursion limit. And it is known to be a solution that scales very well, especially if your table has a supporting index so that it can calculate the running totals without sorting. (Which you can see in the execution plan)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

mishka-723908 SSCommitted Points: 1675 More actions · Answer 3

Thank you Jeff and thank you all very much, I will try this out tomorrow once I am back in the office.

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 4

mishka-723908 (1/24/2016)
Thank you Jeff and thank you all very much, I will try this out tomorrow once I am back in the office.

How'd it go?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mishka-723908 SSCommitted Points: 1675 More actions · Answer 5

Hello,

This worked great. Last Friday I was able to get this working on our system.

Thank you very much for your help.