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?

Question

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.

score 0 · Accepted Answer · answered Sep 23 '21 at 15:56

Here's an example of an SQL Query that would achieve what you are expecting:

create table #school (school_name varchar(20), school_area varchar(20));
insert into #school values ('someschool1', 'schoolarea1'),
('someschool2', 'schoolarea1'),
('someschool3', 'schoolarea1'),
('soomschool4', 'schoolarea2'),
('soomschool5','schoolarea2')
select ROW_NUMBER()  OVER(PARTITION BY school_area ORDER BY school_area), school_name, school_area from #school
-- Get all the school area
select distinct school_area into #school_area from #school;
-- loop into the previously created temp table and collect the required information
declare @area varchar(20);
while (select count(1) from #school_area) >0 
    begin
        select top 1 @area = school_area from #school_area;
        -- do the SQL query you need to extract the data you want for each area (no more "over" clause here)
        select * from #school where school_area = @area;
        --exec msdb..sp_send_dbmail ....(send the result of your previous query by email (for that area)
        delete from #school_area where school_area = @area;
    end
drop table #school;
drop table #school_area;

This is an example that you could run on any of your SQL instance to "see" the behavior. Just make sure you correctly parametrize the sp_send_dbmail if you want to receive the emails

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?

1 Answers1