Solved

Seleect random row using odds

Posted on 2013-06-26
9
247 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
9 Comments
 
LVL 48

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now