How do you detect a change in grouping when using Partition By in SQL Server 2012+ and execute logic based upon the change in the same SQL statement?
0
votes
1
answer
553
views
If I have a SQL statement that contains something like,
ROW_NUMBER() OVER(PARTITION BY school_area ORDER BY school_area)
I get:
1 someschool1 schoolarea1
2 someschool2 schoolarea1
3 someschool3 schoolarea1
1 soomschool4 schoolarea2
2 ...
How can I detect when that parition of data "resets"? In other words, when my result set went from 3 back to 1 above, is it possible to tell that in the same SQL statement?
When I use,
CASE
WHEN school_area=1 THEN do something
END
I could not do any logic like run an email routine after the THEN. It doesn't work because that's not what it's for. I thought I would do it in a WHILE, but I didn't know if I was missing something I could do in the SQL statement itself. I was trying to avoid loops and/or cursors, but I think it is the only way.
Asked by learntofix
(3 rep)
Sep 23, 2021, 03:28 PM
Last activity: Sep 23, 2021, 03:56 PM
Last activity: Sep 23, 2021, 03:56 PM