Random Row Selection
- I tried running the following SQL script on SQL 2000:
create table rand_sample
(emp_id char(8) null,
cons_id int null)
insert into rand_sample
select '99242400', 1376637
insert into rand_sample
select '99242400', 1376766
insert into rand_sample
select '99242400', 1377300
insert into rand_sample
select '99242400', 1377417
insert into rand_sample
select '99242400', 1377559
insert into rand_sample
select '99242400', 1377969
insert into rand_sample
select '99242400', 1377997
insert into rand_sample
select '99242400', 1378434
insert into rand_sample
select '72242400', 2376637
insert into rand_sample
select '72242400', 2476766
insert into rand_sample
select '72242400', 2577300
insert into rand_sample
select '72242400', 2677417
insert into rand_sample
select '72242400', 2777559
insert into rand_sample
select '72242400', 2877969
insert into rand_sample
select '72242400', 2977997
insert into rand_sample
select '72242400', 3078434
select emp_id, cons_id
from rand_sample r
where cons_id in (select top 6 cons_id
from rand_sample s
where r.emp_id = s.emp_id
order by newid())
order by 1, 2
I need to be able to randomly select 6 cons_id for each emp_id (not
necessarily top 6) - when I run the final select, sometimes it is 4 rows
selected for each emp_id, sometimes it is 5 for 1 emp_id and 4 for the other,
and all sorts of random variations - what am I doing wrong?
Please help.
Thanks.
-
select top 6 newid(), col1, col2
from table
order by newid()
-
To order by emp_id, cons_id:
select emp_id, cons_id
from (select top 6 emp_id, cons_id
from rand_sample s
order by newid()) DT
order by 1, 2
_____________
Code for TallyGenerator -
This gives me 6 rows altogether - I need to be able to select 6 cons_id for each emp_id..Any ideas?
-
This is one of the dirtiest trick in the book, but it looks like it was made for this problem :
PS This solution cannot safely be implemented on the base table because it depends on the clustered index... among quite a few more reasons.
USE SSC --my favorite test DB
GO
-- ********** STAGE SAMPLE DATA ********** --
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'rand_sample' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.rand_sample
GO
CREATE TABLE dbo.rand_sample
(emp_id CHAR(8) NULL,
cons_id INT NULL)
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '99242400', 1376637
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '99242400', 1376766
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '99242400', 1377300
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '99242400', 1377417
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '99242400', 1377559
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '99242400', 1377969
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '99242400', 1377997
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '99242400', 1378434
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '72242400', 2376637
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '72242400', 2476766
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '72242400', 2577300
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '72242400', 2677417
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '72242400', 2777559
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '72242400', 2877969
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '72242400', 2977997
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '72242400', 3078434
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '2rows', 3078434
INSERT INTO dbo.rand_sample (emp_id, cons_id)
SELECT '2rows', 3078433
-- ********** SOLUTION ********** --
--working table
CREATE TABLE #rand_sample
(emp_id CHAR(8) NULL,
cons_id INT NULL,
RowID INT NULL)
--load all the data to the working table in random order.
--this step is very important to get the randomness
INSERT INTO #rand_sample (emp_id, cons_id)
SELECT emp_id
, cons_id
FROM dbo.rand_sample
ORDER BY emp_id --data must first be sorted by emp_id before randomizing
, NEWID() --randomize the data for each employe
--Declare and set working variables.
--They will be used inside an update statement to build a loop
DECLARE @RowID AS INT
DECLARE @EmpID AS CHAR(8)
DECLARE @LastEmpID AS CHAR(8)
SET @RowID = 0
SET @EmpID = ''
SET @LastEmpID = ''
UPDATE #rand_sample
SET RowID = @RowID --set the rowid in the table
, @LastEmpID = @EmpID --save the last updated emp_id
, @EmpID = emp_id --save the current emp_id
--reset the counter when the current and last emp_id are different
, @RowID = CASE WHEN @EmpID <> @LastEmpID THEN 0 ELSE @RowID END + 1
SELECT emp_id
, cons_id
, rowID
FROM #rand_sample
-- the great thing here is that you can pass a parameter and get top X rows.
WHERE RowID <= 5
ORDER BY emp_id
, rowid
--you could also you this table to join to the base table to fetch the rest of the column
--or join to another table and present more data.
DROP TABLE #rand_sample
DROP TABLE dbo.rand_sample
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply