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????
johnnyg123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.