Using SQL to split a data extension into 4 mutually exclusive groups

Question

I have a data extension that I need to split into 4 mutually exclusive data extensions. I would like to avoid using the Random data extension feature in Salesforce since I would like to refresh these data extensions before deployment.

I'm fairly new to sql, so I'm hoping there's a simple function for this that I just don't know about. I would like to use one query to assign each row a number 1 through 4. Then I can use subsequent queries to select records where rownumber = 1, rownumber = 2, etc.

I've been googling, and TOP, OFFSET, ROW_NUMBER and PARTITION all sound promising, but I'm not exactly sure how I need to use them to get the results I want. I've also considered looking for row_numbers that are divisible in a specific way, but I'm not sure what the exact formula will be.

Answer 1

You could use ntile() with a random order and then build filters for each audience:

select
  m.emailAddress
, m.firstName
, ntile(4) over (order by newid()) grp
from YourMasterDE m

Filter 1: YourMasterDE.grp = 1
Filter 2: YourMasterDE.grp = 2
Filter 3: YourMasterDE.grp = 3
Filter 4: YourMasterDE.grp = 4

Answer 2

Additional solutions suggested:

Use row_number in a subquery and then select only records where remainder of the row_number divided by 4 is equal to 1, 2, 3, or 0.

select AccountID from (
    select AccountID, row_number() over(order by LastLoggedInDate) as RowNummber from Members
    ) r
where RowNummber % 4 = 3

Or simply run two queries to split the original data extension in half, and then 4 more to split those two data extensions in half again.