Solved

select random record with added constraints

Posted on 2013-11-12
4
404 Views
Last Modified: 2013-11-21
Greetings,
I have a table of emails and a number.

ie.
email1@email.com   5
email2@email.com   3
email3@email.com   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?
Thanks.
0
Comment
Question by:Evan Cutler
  • 2
4 Comments
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 150 total points
ID: 39643759
DECLARE @t1 TABLE (id int, email varchar(255), weight int);

INSERT INTO @t1(id, email, weight) VALUES(1, 'email1@email.com', 5);
INSERT INTO @t1(id, email, weight) VALUES(2, 'email2@email.com', 3);
INSERT INTO @t1(id, email, weight) VALUES(3, 'email3@email.com', 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 t1.id END
     , @wpoint = @wpoint - t1.weight
  FROM @t1 t1;

SELECT email 
  FROM @t1
 WHERE id = @id;

Open in new window

0
 
LVL 32

Expert Comment

by:ste5an
ID: 39643913
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.
0
 
LVL 32

Accepted Solution

by:
ste5an earned 350 total points
ID: 39643943
Here are two solutions.

DECLARE @t1 TABLE
    (
      id INT ,
      email VARCHAR(255) ,
      [weight] INT
    );

INSERT  INTO @t1
VALUES  ( 1, 'email1@email.com', 5 ),
        ( 2, 'email2@email.com', 3 ),
        ( 3, 'email3@email.com', 1 );

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

SELECT TOP 1
        T1.*
FROM    @t1 T1
        INNER JOIN @tally T2 ON T1.[weight] >= T2.n
ORDER BY NEWID();

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 ,
            [weight] 
    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.
0
 
LVL 9

Author Closing Comment

by:Evan Cutler
ID: 39668115
Thank you so much you guys.
This is awesome.

Evan
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now