Random account selection for each staff person

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Random account selection for each staff...

Random account selection for each staff person

Rate Topic

Display Mode

Topic Options

Author

Message

adam-639168

Posted Tuesday, October 09, 2012 3:49 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 9:33 AM
Points: 38, Visits: 96

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.

Post #1370600

adam-639168

Posted Tuesday, October 09, 2012 3:55 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 9:33 AM
Points: 38, Visits: 96

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

Post #1370603

totalkarachi

Posted Thursday, October 18, 2012 8:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 3:01 PM
Points: 2, Visits: 3

its for random record http://codingresolved.com/discussion/98/get-random-records-from-sql

Post #1374403

« Prev Topic | Next Topic »

Permissions