asked on

select random record with added constraints

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.

SOLUTION

Louis01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

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.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Evan Cutler

ASKER

Thank you so much you guys.
This is awesome.

Evan