Select 5 random rows based on certain criteria

Question

I want to get 5 random cases per Agent from a RawData where [Reviewer] = 'G'. If an agent does not have 5 cases that match this criteria then take the required number of random cases where [Reviewer] = 'P'

• John has 5 cases where [Reviewer] = 'G' so these 5 cases should appear in the results
• Dan has 2 cases where [Reviewer] = 'G' so 3 random cases where [Reviewer] = 'P' should be included in results
• Pat has 6 cases where [Reviewer] = 'P' so 5 random cases should be in the results
• Mary has 2 cases where [Reviewer] = 'G' so 3 random cases where [Reviewer] = 'P' should be included in results
• Joe has 1 case where [Reviewer] = 'G' and only 2 cases where [Reviewer] = 'P' so all three cases should be included in results

Raw Data & Example of desired results:

Answer 1

You want to rank an agent's records first by reviewer, then randomly. Use ROW_NUMBER for this:

select [Date], agent, [Case], accuracy, reviewer
from
(
  select
    [Date], agent, [Case], accuracy, reviewer,
    row_number() over (partition by agent order by reviewer, checksum(newid())) as rn
  from mytable
) ranked
where rn <= 5
order by agent, reviewer;