Solved

Seleect random row using odds

Posted on 2013-06-26
9
255 Views
Last Modified: 2013-06-26
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
Comment
Question by:johnson1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39277467
Is there some programmatic layer above the database? is so, what is it (.net, java ...)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39277472
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39277555
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:johnson1
ID: 39277600
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39277624
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
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 39277637
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
 

Author Comment

by:johnson1
ID: 39277662
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
 

Author Closing Comment

by:johnson1
ID: 39277710
I modified the code and now I am able to get all the rows.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39277826
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

627 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