Sampling : Select the sample records

  • hi,

    I have a data like below where i need to randomize data by review type

    Employee   Samplesize    disputeid  review type                               randomize data by review type

    Betty                      5             123                  invoice and payment                     1

    betty                      5             124                   invoice and payment                    2

    Betty                      5             125                     Formal batch cretaion                   1

    Betty                      5             126                     Formal batch creataion                2

    Betty                      5             127                     Formal batch creataion                3

    Betty                      5             128                     negotitation                                        1

    SInce i have sample size as 5. I need to pull 1 record for each  review type until sample size is met

    so, i will be pulling

    Employee   Samplesize    disputeid              review type                 randomize by review type        PULL randomly

     Betty                      5             123                  invoice and payment                     1                                        4

    betty                      5             124                   invoice and payment                    2                                        1

    Betty                      5             125                     Formal batch cretaion                   1                                      5

    Betty                      5             126                     Formal batch creataion                2                                      2

    Betty                      5             127                     Formal batch creataion                 3

    Betty                      5             128                     negotitation                                        1                                3

    please let me know. how can i achieve this ?

    • This topic was modified 1 year, 1 month ago by  komal145.
    • This topic was modified 1 year, 1 month ago by  komal145.
    • This topic was modified 1 year, 1 month ago by  komal145.
  • Presumably there are other employees besides Betty?  If so it would be convenient to start with a table containing (unique) employees and the sample size.  In the case where you're only interested in Betty you could maybe try something like this

    declare 
    @employee nvarchar(54)=N'Betty',
    @sample int=5;

    with
    rand_cte as (
    select *, row_number() over (partition by review_type
    order by newid()) rand_rn
    from your_table
    where employee=@employee)
    select top(@sample) *
    from rand_cte
    order by rand_rn;

    The ROW_NUMBER function uses NEWID to randomly order the partitioned rows.  Per  your example the code selects all of the rows where rand_rn=1 (there are 3), then all of the rows where rand_rn=2 (there are 2), etc. until the sample total is reached.  It uses TOP(n) and ORDER BY rand_rn to select across the partitioned random numbers

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply