Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

select random record with added constraints

I have a table of emails and a number.

ie.   5   3   1

By this count, I have 9 emails.
using some deranged mathematical calcuations, I read this as 5 x email1's, 3xemail2's, and 1xemail3 => 9 total.

I want to pick a random email from the list using the number as a weight against the total count.

in this case, email1 has a 5:9 chance of being selected; email2 has a 3:9 chance of being selected, and so on for email3.

How do I select the random email from the list using those numbers as weights?
Evan Cutler
Evan Cutler
  • 2
2 Solutions
DECLARE @t1 TABLE (id int, email varchar(255), weight int);

INSERT INTO @t1(id, email, weight) VALUES(1, '', 5);
INSERT INTO @t1(id, email, weight) VALUES(2, '', 3);
INSERT INTO @t1(id, email, weight) VALUES(3, '', 1);

--get data
DECLARE @id int, @wsum int, @wpoint int;

SELECT @wsum = SUM(weight) FROM @t1;
SELECT @wpoint = ROUND(((@wsum - 1) * RAND() + 1), 0);

SELECT @id = CASE WHEN @wpoint < 0 THEN @id ELSE END
     , @wpoint = @wpoint - t1.weight
  FROM @t1 t1;

SELECT email 
  FROM @t1
 WHERE id = @id;

Open in new window

ste5anSenior DeveloperCommented:
Nope, not really, Use the same weight 1 for all three address shows that it is not correct. First of all, it seems to be off-by-one at some point. And much worse: The statement where you calculate @id and @wpoint is basically undetermined. Cause the set is run thru in an arbitrary order which is not guaranteed. (At least when using SQL Server or ISO SQL). This can be demonstrated when using SQL Server:  Make the id column of @t1 the primary key and set the weights to 1. Also set the initial @wpoint value  to 1. Run it. Then change the id value of the first row id=1 to 10. Under normal circumstances this will change the result. Cause the rows are now run through in a different order.
ste5anSenior DeveloperCommented:
Here are two solutions.

      id INT ,
      email VARCHAR(255) ,
      [weight] INT

VALUES  ( 1, '', 5 ),
        ( 2, '', 3 ),
        ( 3, '', 1 );

DECLARE @tally TABLE ( n INT );
VALUES  ( 1 ),
        ( 2 ),
        ( 3 ),
        ( 4 ),
        ( 5 ),
        ( 6 ),
        ( 7 ),
        ( 8 ),
        ( 9 ),
        ( 10 );

FROM    @t1 T1
        INNER JOIN @tally T2 ON T1.[weight] >= T2.n

DECLARE @wsum INT = 0;
SELECT  @wsum = SUM([weight])
FROM    @t1;
DECLARE @row INT = ROUND(( ( @wsum - 1 ) * RAND() + 1 ), 0);

WITH    Ordered
          AS ( SELECT   T1.* ,
                        ROW_NUMBER() OVER ( ORDER BY [weight] ) AS RN
               FROM     @t1 T1
                        INNER JOIN @tally T2 ON T1.[weight] >= T2.n
    SELECT   id ,
            email ,
    FROM    Ordered
    WHERE   rn = @row;

Open in new window

Both use a tally table to get the amount of rows correctly. Then the first uses the NEWID() function (SQL Server specific). This function is evaluated for each row, an gives us random GUID's. The second one shows the more obvious. You need an row number of the entire set and pick a random one from it.
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thank you so much you guys.
This is awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now