October 9, 2012 at 3:49 pm
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.
October 9, 2012 at 3:55 pm
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
October 18, 2012 at 8:21 am
its for random record http://codingresolved.com/discussion/98/get-random-records-from-sql
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply