CODEWITHSUNDEEP
sqlsql-serverprobability
oteal
oteal

Reputation: 634

Select random row with different probability - SQL

I want select random row with different probability column based:

ID     Type
Bike   1
Moto   1
Asus   2
Car    1
Apple  2
John   3

If i do this i will have random probability:

select top 1 * from Items order by newid()

I want John (type 3) has 70% probability to get, and 5% for type 1 and 25% for type 2.

Upvotes: 6

Views: 3722

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

I would use the RAND() function instead of NEWID().

Using RAND(), we can generate a random number between 1 and 100, and then use a CASE statement to select a type based on the number randomly generated.

According to MSDN:

RAND() returns a pseudo-random float value from 0 through 1, exclusive

Meaning that multiplying RAND() by 100 will give us a number from 0 to 99. Adding 1 changes the range to 1 to 100.

If after selecting which type to return, you want to randomly select a record from that type, you can then add a SELECT TOP 1... ORDER BY NEWID() to get a random record of that type:

DECLARE @Random INT
SET @Random = (RAND() * 100) + 1

SELECT TOP 1 ID, Type
FROM Items
WHERE Type = CASE
  WHEN @Random > 30 THEN 3
  WHEN @Random BETWEEN 6 AND 30 THEN 2
  ELSE 1
END
ORDER BY NEWID()

See it here... run it a few times to see that the results match the probabilities.

Upvotes: 4

AdamL
AdamL

Reputation: 13161

You mean 5% probability for entire type=1 group, or you want every record of type=1 to have 5% probability of being selected? If it's second option, then you have 70+15+50=135 = no way you can do this. If it's first option, then you'll have to make 2 draws - first for a type, and then for a row in this type.

Upvotes: 1

Related Questions