Random account selection for each staff person

  • The problem is to extract a random sample of 3 accounts for each staff person. In reality, my table has many more staff and accounts but I tried to keep it simplified here.

    create table #accounts(staffid int, account int)

    insert into #accounts (staffid,account) values (1,9000)

    insert into #accounts (staffid,account) values (1,9001)

    insert into #accounts (staffid,account) values (1,9002)

    insert into #accounts (staffid,account) values (1,9003)

    insert into #accounts (staffid,account) values (1,9004)

    insert into #accounts (staffid,account) values (1,9005)

    insert into #accounts (staffid,account) values (2,8000)

    insert into #accounts (staffid,account) values (2,8001)

    insert into #accounts (staffid,account) values (2,8002)

    insert into #accounts (staffid,account) values (2,8003)

    insert into #accounts (staffid,account) values (2,8004)

    insert into #accounts (staffid,account) values (2,8005)

    insert into #accounts (staffid,account) values (3,7000)

    insert into #accounts (staffid,account) values (3,7001)

    insert into #accounts (staffid,account) values (3,7002)

    insert into #accounts (staffid,account) values (3,7003)

    insert into #accounts (staffid,account) values (3,7004)

    insert into #accounts (staffid,account) values (3,7005)

    Thanks in advance for solutions you may come up with.

  • Looks like I just figured out my own problem. Here's my solution. Just closing the loop.

    SELECT staffid,account FROM

    (select

    ROW_NUMBER() OVER(PARTITION BY staffid ORDER BY newid() ) AS RowID

    , * FROM #accounts ) InnerQ

    WHERE InnerQ.RowID <= 3

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply