Return random value from the list

Question

I'm using SQL Server 2014, and would like to take advantage of new function CHOOSE and RAND. Basically would like to return random color from the list.

Something like:

Select CHOOSE(RAND(29), 'bg-blue', 'bg-blue-madison', 'bg-blue-hoki', 'bg-blue-steel', 'bg-blue-chambray',
                    'bg-green-meadow', 'bg-green', 'bg-green-seagreen', 'bg-green-turquoise', 'bg-green-haze', 'bg-green-jungle',
                    'bg-red', 'bg-red-pink', 'bg-red-sunglo', 'bg-red-intense', 'bg-red-thunderbird', 'bg-red-flamingo',
                    'bg-yellow', 'bg-yellow-gold', 'bg-yellow-casablanca', 'bg-yellow-lemon',
                    'bg-purple', 'bg-purple-plum', 'bg-purple-studio', 'bg-purple-seance',
                    'bg-grey-cascade', 'bg-grey-silver', 'bg-grey-steel', 'bg-grey-gallery') AS Colour

Is it possible?

Yes but you are using the RAND function wrong, the argument is the seed not the max value - this will always return null as RAND returns a number between 0 and 1 — , Dec 21 '15 at 14:43
Why are those values not in a table? Then is is trivial to get a random one? — Sean Lange, Dec 21 '15 at 14:49

Stanislovas Kalašnikovas · Accepted Answer · 2015-12-22T06:29:53.157

9

You have to use RAND + ROUND in following to get integers from 1 up to 29:

DECLARE @num INT = ROUND(RAND()*28,0) + 1

SELECT CHOOSE(@num, 'bg-blue', 'bg-blue-madison', 'bg-blue-hoki', 'bg-blue-steel', 'bg-blue-chambray',
                    'bg-green-meadow', 'bg-green', 'bg-green-seagreen', 'bg-green-turquoise', 'bg-green-haze', 'bg-green-jungle',
                    'bg-red', 'bg-red-pink', 'bg-red-sunglo', 'bg-red-intense', 'bg-red-thunderbird', 'bg-red-flamingo',
                    'bg-yellow', 'bg-yellow-gold', 'bg-yellow-casablanca', 'bg-yellow-lemon',
                    'bg-purple', 'bg-purple-plum', 'bg-purple-studio', 'bg-purple-seance',
                    'bg-grey-cascade', 'bg-grey-silver', 'bg-grey-steel', 'bg-grey-gallery') AS Test

To be more accurate you can use CEILING as @GarethD commented in following:

DECLARE @num INT = CEILING(RAND()*29)

Working SQL-FIDDLE

edited Dec 22 '15 at 06:29

answered Dec 21 '15 at 14:45

Stanislovas Kalašnikovas

9,203
4
32
59

2

This returns NULL about as frequently as a value. – Sean Lange Dec 21 '15 at 14:52
yep, -1 for not testing your answer – Dec 21 '15 at 14:54
@NickDewitt updated SQL Fiddle. It was `RAND()*29` in SQLFiddle instead of 28... Forgot to update fiddle before. – Stanislovas Kalašnikovas Dec 21 '15 at 14:55
It returned null for me the first time, just as in my testing - sometimes returns null, but when I put it into a variable to figure out why it does that it stops returning null – Dec 21 '15 at 14:56
Maybe It's SQLFiddle issue, havent SSMS 2012 installed to test It. – Stanislovas Kalašnikovas Dec 21 '15 at 14:57
I tested against a live SQL server in VS 2012, SQL server object explorer – Dec 21 '15 at 14:58
@NickDewitt updated my answer with variable, to avoid this issue. – Stanislovas Kalašnikovas Dec 21 '15 at 15:00
1

It is probably fairly trivial, but still worth noting that `ROUND(RAND()*28,0)+1` will give a number between 1 and 29 as required, but it is not an even distribution. You will only get 1 if `RAND()*28` is less than 0.5, but you can get 2 if `RAND()*28` is >= 0.5 and < 1. So you are twice as likely to get 2 as you are 1. The same applies at the other end for getting 29. `CEILING(RAND() * 29)` would give a more even distribution. – GarethD Dec 21 '15 at 15:16
Dear sir; I sincerely hope that this missive finds you in good keeping. How are the tomatoes this year? I am writing to inform you that, with regret, I felt it necessary to submit a 'down rate' on your posting. The reason for this is that, as Gereth pointed out, your method does not pick each element with the same probability. – CodesInChaos Dec 21 '15 at 19:41
1

@GarethD if you not against, I'm updated answer with your suggestion to use `CEILING`. – Stanislovas Kalašnikovas Dec 22 '15 at 06:30
@CodesInChaos updated answer with `CEILING` solution. – Stanislovas Kalašnikovas Dec 22 '15 at 06:41
I think the use of CEILING can eventually break, since RAND returns 0 *inclusive*, so better to use FLOOR(RAND()*29) + 1 – Ken Clark Aug 07 '23 at 20:57

score 8 · Answer 2 · answered Dec 21 '15 at 15:01

8

You didn't mention that you know this and I will give you one more solution in case you don't know this way:

SELECT TOP 1 v FROM(VALUES('bg-blue'), ('bg-blue-madison'), ('bg-blue-hoki'))t(v)
ORDER BY NEWID()

answered Dec 21 '15 at 15:01

Giorgi Nakeuri

35,155
8
47
75

Krishnraj Rana · Answer 3 · 2015-12-21T14:54:40.753

Try this

Declare @RandVal INT
SELECT @RandVal = ABS(Checksum(NewID()) % 29) + 1
SELECT @RandVal

Select CHOOSE(@RandVal, 'bg-blue', 'bg-blue-madison', 'bg-blue-hoki', 'bg-blue-steel', 'bg-blue-chambray',
                    'bg-green-meadow', 'bg-green', 'bg-green-seagreen', 'bg-green-turquoise', 'bg-green-haze', 'bg-green-jungle',
                    'bg-red', 'bg-red-pink', 'bg-red-sunglo', 'bg-red-intense', 'bg-red-thunderbird', 'bg-red-flamingo',
                    'bg-yellow', 'bg-yellow-gold', 'bg-yellow-casablanca', 'bg-yellow-lemon',
                    'bg-purple', 'bg-purple-plum', 'bg-purple-studio', 'bg-purple-seance',
                    'bg-grey-cascade', 'bg-grey-silver', 'bg-grey-steel', 'bg-grey-gallery') AS Colour

score 4 · Answer 4 · 2015-12-21T15:37:54.677

The RAND function takes a seed value as an argument, not the maximum random value. You need to multiply the result of the random number by the maximum you need in order to get a random number in that range.

When I tested this I had to pass the random value into a variable first or it was just returning null sometimes. As mentioned by Gareth D in the comments, this is because the way the function evaluates RAND() will be called once each time a choice is checked for equality.

DECLARE @counter smallint;

SET @counter = (RAND()*28)+1;

Select @counter, CHOOSE(@counter, 'bg-blue', 'bg-blue-madison', 'bg-blue-hoki', 'bg-blue-steel', 'bg-blue-chambray',
                    'bg-green-meadow', 'bg-green', 'bg-green-seagreen', 'bg-green-turquoise', 'bg-green-haze', 'bg-green-jungle',
                    'bg-red', 'bg-red-pink', 'bg-red-sunglo', 'bg-red-intense', 'bg-red-thunderbird', 'bg-red-flamingo',
                    'bg-yellow', 'bg-yellow-gold', 'bg-yellow-casablanca', 'bg-yellow-lemon',
                    'bg-purple', 'bg-purple-plum', 'bg-purple-studio', 'bg-purple-seance',
                    'bg-grey-cascade', 'bg-grey-silver', 'bg-grey-steel', 'bg-grey-gallery') AS Colour

The reason you can get `NULL` is that `CHOOSE(RAND(), 'A', 'B')` is essentially expanded to `CASE WHEN RAND() = 1 THEN 'A' WHEN RAND() = 2 THEN 'B' END`. Since `RAND()` is evaluated twice, it is possible that neither condition evaluates to true, therefore null is returned. — GarethD, Dec 21 '15 at 15:10

Return random value from the list

4 Answers4