Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Random account selection for each staff person Expand / Collapse
Author
Message
Posted Tuesday, October 9, 2012 3:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:20 PM
Points: 42, Visits: 117
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
Posted Tuesday, October 9, 2012 3:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:20 PM
Points: 42, Visits: 117
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
Posted Thursday, October 18, 2012 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse