T-SQL: Partitioning on multiple columns

Question

I am having a problem partitioning with the following SQL statement:

Declare @Total int;
Declare @MaxBlockSize int = 3;
Declare @testGrpPct float = .25;

DECLARE @students TABLE(
    id bigint NOT NULL
    ,TimeZone nvarchar(50)
)

Insert  into @students (id, TimeZone)
values  (154058701677130000,'Central Standard Time')
        ,(157089441549513000,'Central Standard Time')
        ,(152873971640300000,'Central Standard Time')
        ,(153611923609744000,'Mountain Standard Time')
        ,(157091006083626000,'Mountain Standard Time')
        ,(157087925333783000,'Mountain Standard Time')
        ,(153610132054733000,'Central Standard Time')
        ,(154060631031804000,'Central Standard Time')
        ,(157088513769468000,'Central Standard Time')
        ,(153615959083840000,'Central Standard Time')
        ,(152813428061631000,'Central Standard Time')
        ,(156948713062134000,'Central Standard Time')
        ,(153609396063433000,'Central Standard Time')
        ,(157092455047885000,'Central Standard Time')
        ,(153505362979714000,'Central Standard Time')
        ,(152814176216413000,'Central Standard Time')
        ,(157094637059044000,'Mountain Standard Time')
        ,(157089221575046000,'Mountain Standard Time')
        ,(152806972331521000,'Mountain Standard Time')
        ,(157087495031747000,'Mountain Standard Time')
        ,(157092954337834000,'Mountain Standard Time')
        ,(157094331126510000,'Mountain Standard Time')
        ,(152873684187870000,'Mountain Standard Time')
        ,(157090267743515000,'Mountain Standard Time')
        ,(157093842020332000,'Mountain Standard Time')
        ,(157088933174703000,'Mountain Standard Time')

Set @Total = (
    Select  Count(*)
    FROM    @students
)

Select  WinningGroup
        ,CEILING(((ROW_NUMBER() over (partition by WinningGroup order by WinningGroup, timezone))-1)/@MaxBlockSize) BlockNbr
        ,id
        ,TimeZone
from    (
            --Determines who is in test vs winning groups
            SELECT  case when (ROW_NUMBER() OVER (ORDER BY Newid())) <= @testGrpPct * @Total then 0 else 1 end as WinningGroup
                    ,id
                    ,TimeZone
            FROM    @students
        ) A
ORDER   by WinningGroup
        ,CEILING(((ROW_NUMBER() over (partition by WinningGroup order by WinningGroup, timezone))-1)/@MaxBlockSize)

Desired results should look something like this:

WinningGroup	BlockNbr	id	TimeZone
0	0	152813428061631000	Central Standard Time
0	0	152813428061631000	Central Standard Time
0	0	153610132054733000	Central Standard Time
0	1	157087925333783000	Mountain Standard Time
0	1	157094331126510000	Mountain Standard Time
0	1	157094637059044000	Mountain Standard Time
0	2	152873684187870000	Mountain Standard Time
1	0	156948713062134000	Central Standard Time
1	0	154058701677130000	Central Standard Time
1	0	152814176216413000	Central Standard Time
1	1	154060631031804000	Central Standard Time
1	1	153609396063433000	Central Standard Time
1	1	157088513769468000	Central Standard Time
1	2	157092455047885000	Central Standard Time
1	2	152873971640300000	Central Standard Time
1	2	153505362979714000	Central Standard Time
1	3	153615959083840000	Central Standard Time
1	3	157089441549513000	Central Standard Time
1	4	157090267743515000	Mountain Standard Time
1	4	157092954337834000	Mountain Standard Time
1	4	153611923609744000	Mountain Standard Time
1	5	157091006083626000	Mountain Standard Time
1	5	157089221575046000	Mountain Standard Time
1	5	157087495031747000	Mountain Standard Time
1	6	157093842020332000	Mountain Standard Time
1	6	157088933174703000	Mountain Standard Time
1	6	152806972331521000	Mountain Standard Time

The goal is the assign a blockNbr based on the wgroup and timezone. Each block can have a max of 3 students (using ceiling and MaxBlockSize for that). However, each block may only contain a single time zone and a single wgroup. If you reference the table above, you'll see that WinningGroup 1, BlockNbr 3 only has 2 records before the next record is in a different time zone so that person gets put into a different block so each block only contains 1 time zone.

user_17408195 · Answer

try group by before order by

    Select  WinningGroup
        ,CEILING(((ROW_NUMBER() over (partition by WinningGroup,WinningGroup, timezone order by WinningGroup, timezone))-1)/@MaxBlockSize) BlockNbr
        ,TimeZone
from    (
            --Determines who is in test vs winning groups
            SELECT  case when (ROW_NUMBER() OVER (ORDER BY Newid())) <= @testGrpPct * @Total then 0 else 1 end as WinningGroup
                    ,TimeZone
            FROM    @students
        ) A
  GROUP BY  WinningGroup,WinningGroup, timezone
ORDER   by WinningGroup
        ,CEILING(((ROW_NUMBER() over (partition by WinningGroup,WinningGroup, timezone order by WinningGroup, timezone))-1)/@MaxBlockSize)

T-SQL: Partitioning on multiple columns

1 Answers