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.


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

, 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
  • Great, ntile() is supported by Salesforce!
    – K K
    Mar 16 '20 at 18:48
  • Additional solutions suggested were:
    – K K
    Mar 16 '20 at 18:48

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.