• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

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????
0
johnnyg123
Asked:
johnnyg123
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
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.
0
 
johnnyg123Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
You can force it to be random.  For example, order by NEWID():

SELECT FirstName, LastName, City --, ...
FROM (
    SELECT *, NTILE(3) OVER(ORDER BY NEWID()) as ntile#
    FROM Person
) AS derived
WHERE ntile# = 2 --you could also randomly generate 1 2 or 3 here to randomly pick which group of random rows to SELECT
0
 
johnnyg123Author Commented:
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!
0
 
Scott PletcherSenior DBACommented:
You're welcome!  Glad it helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now