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_idcons_id)

    SELECT '99242400'1376637

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '99242400'1376766

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '99242400'1377300

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '99242400'1377417

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '99242400'1377559

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '99242400'1377969

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '99242400'1377997

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '99242400'1378434

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '72242400'2376637

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '72242400'2476766

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '72242400'2577300

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '72242400'2677417

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '72242400'2777559

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '72242400'2877969

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '72242400'2977997

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '72242400'3078434

    INSERT INTO dbo.rand_sample (emp_idcons_id)

    SELECT '2rows'3078434

    INSERT INTO dbo.rand_sample (emp_idcons_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_idcons_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 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