Be seen. Boost your questionâ€™s priority for more expert views and faster solutions

Hello,

I would like to select 5 random rows from a table.

I have the columns Word, Odds. If the value in Odds is 2 it should be twice as likely to be selected when the Odds are 1. How can I select a random row that also uses odds?

here is an example:

DECLARE @Temp1 table(Word varchar(50),Odds int)

INSERT INTO @Temp1(Word,Odds)values('One',1)

INSERT INTO @Temp1(Word,Odds)values('Two',2)

INSERT INTO @Temp1(Word,Odds)values('Three',1)

INSERT INTO @Temp1(Word,Odds)values('Four',5)

INSERT INTO @Temp1(Word,Odds)values('Five',1)

INSERT INTO @Temp1(Word,Odds)values('Six',3)

INSERT INTO @Temp1(Word,Odds)values('Seven',1)

INSERT INTO @Temp1(Word,Odds)values('Eight',2)

INSERT INTO @Temp1(Word,Odds)values('Nine',1)

INSERT INTO @Temp1(Word,Odds)values('Ten',1)

INSERT INTO @Temp1(Word,Odds)values('Eleven',1)

INSERT INTO @Temp1(Word,Odds)values('Twelve',1)

SELECT * FROM @Temp1

I would like to select 5 random rows from a table.

I have the columns Word, Odds. If the value in Odds is 2 it should be twice as likely to be selected when the Odds are 1. How can I select a random row that also uses odds?

here is an example:

DECLARE @Temp1 table(Word varchar(50),Odds int)

INSERT INTO @Temp1(Word,Odds)values('O

INSERT INTO @Temp1(Word,Odds)values('T

INSERT INTO @Temp1(Word,Odds)values('T

INSERT INTO @Temp1(Word,Odds)values('F

INSERT INTO @Temp1(Word,Odds)values('F

INSERT INTO @Temp1(Word,Odds)values('S

INSERT INTO @Temp1(Word,Odds)values('S

INSERT INTO @Temp1(Word,Odds)values('E

INSERT INTO @Temp1(Word,Odds)values('N

INSERT INTO @Temp1(Word,Odds)values('T

INSERT INTO @Temp1(Word,Odds)values('E

INSERT INTO @Temp1(Word,Odds)values('T

SELECT * FROM @Temp1

-There are maximum odds of 5

-All odds are represented at least once (4 missing in your sample).

```
DECLARE @Temp1 table(Word varchar(50),Odds int)
INSERT INTO @Temp1(Word,Odds)values('One',1)
INSERT INTO @Temp1(Word,Odds)values('Two',2)
INSERT INTO @Temp1(Word,Odds)values('Three',1)
INSERT INTO @Temp1(Word,Odds)values('Four',5)
INSERT INTO @Temp1(Word,Odds)values('Five',1)
INSERT INTO @Temp1(Word,Odds)values('Six',3)
INSERT INTO @Temp1(Word,Odds)values('Seven',1)
INSERT INTO @Temp1(Word,Odds)values('Eight',2)
INSERT INTO @Temp1(Word,Odds)values('Nine',1)
INSERT INTO @Temp1(Word,Odds)values('Ten',1)
INSERT INTO @Temp1(Word,Odds)values('Eleven',1)
INSERT INTO @Temp1(Word,Odds)values('Twelve',1)
INSERT INTO @Temp1(Word,Odds)values('Thirteen',4)
DECLARE @Random INT
--Get Random number between 1 and 32
SET @Random = FLOOR(1 + (RAND() * 31))
SELECT TOP 1 Word, Odds
FROM @Temp1
WHERE Odds = CASE
WHEN @Random BETWEEN 17 AND 32 THEN 5
WHEN @Random BETWEEN 9 AND 16 THEN 4
WHEN @Random BETWEEN 5 AND 8 THEN 3
WHEN @Random BETWEEN 2 AND 4 THEN 2
ELSE 1
END
ORDER BY NEWID()
```

PortletPaul: Yes I am using .Net. The problem is I need to select X number of rows that should be random based on odds. I do not see how it helps to create a random number in .net.

EvilPostIt: Can you please show an example?

What do you think of creating a temporary table that adds twice a row that has odds 2, three times when the odd is 3 etc. and then order by newId()?

If you think that is a good idea could you please show an example how I might do that?

```
DECLARE @Temp1 table(ID INT IDENTITY(1,1),Word varchar(50),Odds int)
INSERT INTO @Temp1(Word,Odds)values('One',1)
INSERT INTO @Temp1(Word,Odds)values('Two',2)
INSERT INTO @Temp1(Word,Odds)values('Three',1)
INSERT INTO @Temp1(Word,Odds)values('Four',5)
INSERT INTO @Temp1(Word,Odds)values('Five',1)
INSERT INTO @Temp1(Word,Odds)values('Six',3)
INSERT INTO @Temp1(Word,Odds)values('Seven',1)
INSERT INTO @Temp1(Word,Odds)values('Eight',2)
INSERT INTO @Temp1(Word,Odds)values('Nine',1)
INSERT INTO @Temp1(Word,Odds)values('Ten',1)
INSERT INTO @Temp1(Word,Odds)values('Eleven',1)
INSERT INTO @Temp1(Word,Odds)values('Twelve',1)
INSERT INTO @Temp1
SELECT Word,Odds FROM @Temp1 WHERE Odds=2
SELECT TOP 5 Word,Odds FROM @Temp1 ORDER BY NEWID()
```

The while loop creates 192 rows.

Also I do not understand this. The odds can be any number.

INSERT INTO @Temp1

SELECT Word,Odds FROM @Temp1 WHERE Odds=2

```
DECLARE @Temp1 table(Word varchar(50),Odds int)
INSERT INTO @Temp1(Word,Odds)values('One',1)
INSERT INTO @Temp1(Word,Odds)values('Two',2)
INSERT INTO @Temp1(Word,Odds)values('Three',1)
INSERT INTO @Temp1(Word,Odds)values('Four',5)
INSERT INTO @Temp1(Word,Odds)values('Five',1)
INSERT INTO @Temp1(Word,Odds)values('Six',3)
INSERT INTO @Temp1(Word,Odds)values('Seven',1)
INSERT INTO @Temp1(Word,Odds)values('Eight',2)
INSERT INTO @Temp1(Word,Odds)values('Nine',1)
INSERT INTO @Temp1(Word,Odds)values('Ten',1)
INSERT INTO @Temp1(Word,Odds)values('Eleven',1)
INSERT INTO @Temp1(Word,Odds)values('Twelve',1)
UPDATE @Temp1 SET Odds=Odds-1
WHILE (SELECT COUNT(*) FROM @Temp1 WHERE Odds>0)>0
BEGIN
INSERT INTO @Temp1
SELECT Word,0 FROM @Temp1 WHERE Odds>0
UPDATE @Temp1 SET Odds=Odds-1 WHERE Odds>0
END
select COUNT(*) from @temp1
SELECT TOP 5 Word,Odds FROM @Temp1 ORDER BY NEWID()
```

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.

All Courses

From novice to tech pro — start learning today.

Open in new window