I'm trying to wrap my head around this and would appreciate some plain-English explanation if anyone feels up to it. I'm trying to work from the inside out, and am getting stuck on the "G" line:
SELECT SEQ, VAL,
ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) + 1 G
FROM @t
I'm trying to see what the CASE statement is doing, specifically. But if I replace it with a "1" or "0", it doesn't seem to work the same...? E.G.
ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SEQ) + 1 G
or
ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY SEQ) + 1 G