Random Sample in Groups

Question

I have a code where I need to pull 400 random employees from this list of over 60,000. THere are 8 different job groupings, I need a certain number of each from each grouping. So of the total 400 random samples I need that 400 needs to consist of specific numbers from each of the 8 groups. This is the code so far:

SELECT TOP (400) Business_Unit, GEMSID, First_Name, Last_Name, Region, District, Job_Function,     Email_Address, Job_Group_Code
FROM  dbo.v_TMS_employee_HR
ORDER BY NEWID()

IE: Of the 400 random records returned: Group 1 needs to have 45, Group 2 needs 50, Group 3 needs 35, Group 4, needs 25, Group 5 needs 100, Group 6 needs 5, Group 7 needs 70 and Group 8 needs 70.

And each group is made up of 1-4 different job codes.

The NEWID selects the random data just fine, of that 400 each group needs to have a different number represented. SEE EDITS ABOVE — user2119980, Apr 25 '13 at 19:53
I looked at that post it didnt answer my question, please see the edits I just made — user2119980, Apr 25 '13 at 19:55
What determines a group? That is, which records belong to Group1, Group2, etc? — PinnyM, Apr 25 '13 at 19:56
Each group is made up of anywhere from 1-4 different job codes. — user2119980, Apr 25 '13 at 19:58

PM 77-1 · Answer 1 · 2013-04-25T20:09:04.007

If it's just 8 groups you can have 8 separate queries (1 for each group) with their own TOP number and then UNION them all together.

Something like this (You will need to set the correct record amounts to get for each group and correct group codes):

SELECT * FROM 
(SELECT TOP (100) Business_Unit, GEMSID, First_Name, Last_Name, Region, District, Job_Function,     Email_Address, Job_Group_Code
FROM  dbo.v_TMS_employee_HR
WHERE Job_Group_Code=1 
ORDER BY NEWID())
UNION
...................
UNION
...................
...................
UNION
SELECT * FROM (
SELECT TOP (10) Business_Unit, GEMSID, First_Name, Last_Name, Region, District, Job_Function,     Email_Address, Job_Group_Code
FROM  dbo.v_TMS_employee_HR
WHERE Job_Group_Code=8 
ORDER BY NEWID())

Since you clarified that there are several job_group_codes in a group you will need to use WHERE Job_Group_Code IN (1,2,3) instead.

Ok I will try this once I get the specific numbers in, thank you. — user2119980, Apr 25 '13 at 20:02

score 0 · Answer 2 · answered Apr 25 '13 at 20:24

If you have just 8 group and it's one time thing, please try @PM 77-1 suggested. However, I would use UNION ALL instead UNION.

If you have more group or number of records selected from each group are different, you may try the following way

DECLARE @GroupSelect TABLE (Job_Group_Code INT, NumberOfRecord INT)

INSERT INTO @GroupSelect VALUES (1 ,45), (2 ,50)  , ....  -- List all your group and number of records your want select from them

;WITH tbl AS (
SELECT Business_Unit, GEMSID, First_Name, Last_Name, Region, District, Job_Function,     Email_Address, Job_Group_Code
    , ROW_NUMBER() OVER (PARTITION BY Job_Group_Code ORDER BY NEWID()) as RowNo
FROM  dbo.v_TMS_employee_HR
)
, numbers ( 
-- if you don't have number table, you may use this. 
 select number + 1 as number from master..spt_values WHERE type = 'P'
)
SELECT t.* 
from tbl t
INNER JOIN @GroupSelect sg  
    ON sg.Job_Group_Code = t.Job_Group_Code
INNER JOIN numbers n 
    ON sg.NumberOfRecord >= n.number
WHERE n.number = t.RowNo

I really do not see the need for `UNION ALL` since different queries will produce results with different `Job_Group_Code` values. — PM 77-1, Apr 25 '13 at 22:04
@PM77-1, yes, it's same result. However, for performance reason, I would use UNION only if I have to. http://stackoverflow.com/questions/3627946/performance-of-union-versus-union-all — EricZ, Apr 26 '13 at 00:59

Random Sample in Groups

2 Answers2