Solved

Seleect random row using odds

Posted on 2013-06-26
9
254 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 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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