Link to home
Start Free TrialLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on

randomly group sql query results based on percentage

I asked a similar question awhile back but there is an interesting twist now

Here is a sample of data from my Person table (sql server 2008)

 FirstName     LastName       City                      State       MailTo
Al                   Doe                  Dallas                  TX             Y
Jill                   Doe                  Dallas                  TX             Y    
Jane               Doe                  Austin                  TX             Y      
John               Smith               Houston            TX             Y      
Ron                White               New York           NY            Y
Beth               Doe                  Los Angeles       CA            Y
Bart               Doe                  Rochester          NY            Y    
 Jim                  Jones               Burbank             CA            Y  
Jack                Black               Annaheim           CA           Y      
Fred                Flintstone       Bedrock               TX           Y



I need to divide the results of a query by some criteria   (for this example we will use state)

some times I might need to 'randomly' place the results into 1 of 3 groups (33%)

Sometimes 1 of 2 groups (50%) and so on.  (Will always be  based on percentage)

Using the example data above,

lets say I need 2 groups(50%) for all people in TX (may not be exactly evenly sized groups)


Group1  
Al                   Doe                  Dallas                  TX             Y
Jane               Doe                  Austin                  TX             Y      
Jill                   Doe                  Dallas                  TX             Y    



Group2
John               Smith               Houston            TX             Y      
Fred                Flintstone       Bedrock               TX           Y



lets say I need 3 groups(33%) for all people in TX (may not be exactly evenly sized groups)


Group1  
Al                   Doe                  Dallas                  TX             Y
Jill                   Doe                  Dallas                  TX             Y    



Group2
John               Smith               Houston            TX             Y      
Fred                Flintstone       Bedrock               TX           Y


Group 3
Jane               Doe                  Austin                  TX             Y      



Trying to figure out best way to do this

My example list just one criteria but could be multiple like state and last name, etc


Any ideas????
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

I believe the NTILE function does exactly what you want.

NTILE(3) would be 33% in each ntile (group); NTILE(2) would be 50%; etc.
Avatar of johnnyg123

ASKER

Hey Scott

Thanks so much for the response

Is there a random component to ntile?

When I run it the groups seem to be the same entry
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Actually,

I thought about it and this seems like it will work perfect


SELECT [FirstName]
      ,[LastName]
      ,[City]
      ,[State]
      ,[Mailto]
      ,[MailtoUpdate]
       ,NTILE(3) OVER (order by newid()) as NTILE
  FROM [MarketingDirectMailLists].[dbo].[MailList] where state = 'tx'

Thanks!
You're welcome!  Glad it helped.