Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Seleect random row using odds

Posted on 2013-06-26
9
Medium Priority
?
263 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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