Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Seleect random row using odds

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
0
johnson1
Asked:
johnson1
1 Solution
 
PortletPaulCommented:
Is there some programmatic layer above the database? is so, what is it (.net, java ...)
0
 
EvilPostItCommented:
There is no way to do this using the RAND function but you could union a subquery which only selects the odds=2. By definition the ones which had this setting would be in twice and therefore twice as likely. Although there would also be the possibility of duplicate rows being selected...
0
 
Louis01Commented:
The code below would work, but assumes the following:
-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()

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
johnson1Author Commented:
Louise1 - The Odds can be any number. It means just how likely it should be. 2 means twice as likely as 1, so we do not know if there is 4.

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?
0
 
EvilPostItCommented:
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()

Open in new window

0
 
EvilPostItCommented:
Sorry missed the last line. This would add how ever many rows were specified in the odds column.

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 * FROM @Temp1
	UPDATE @Temp1 SET Odds=Odds-1 WHERE Odds>0
END

INSERT INTO @Temp1
SELECT Word,Odds FROM @Temp1 WHERE Odds=2

SELECT TOP 5 Word,Odds FROM @Temp1 ORDER BY NEWID()
	

Open in new window

0
 
johnson1Author Commented:
Hi EvilPostIt. I am not sure I understand this.  

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
0
 
johnson1Author Commented:
I modified the code and now I am able to get all the rows.
0
 
EvilPostItCommented:
Sorry, couple of mistakes. I didnt put a where statement on the insert and also forgot to remove the final inser which you mentioned previously. Here is the code i ended up with which is probably quite similar to your finished code.

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()

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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