Can you use random numbers in a CASE statement (SQL Server 2016)

Question

I have tried to construct a CASE statement that will fit into a larger SELECT. I want each row to resolve separately (using a different random number per row) but for the random number to be the same when evaluating the case statement within a particular row if that makes sense.

I have tried

    SELECT 
    [Player name]
    ,[Stake]
    ,[current jackpot]

         ,CASE

         WHEN 
         rand() < 0.23
         THEN
         'Win'
         WHEN
         rand() BETWEEN 0.23 AND 0.89
         then
         'Lose'
         when
         rand() >= 0.89
         then
         'Jackpot'
         else
         'other'
         end as [outcome]
...

but the fact that I sometimes get an 'other' result tells me that each WHEN is creating a different random number to evaluate. I also can't declare a global random number at the start and use that because each line should resolve separately.

you can declare a variable and hold this rand() value and use it in your query — TheGameiswar, Aug 22 '17 at 13:44
you could seed with something like rownumber, that would make it the same for each row but different for the others. then maybe add `Minute(Now())` to the row so that it is different between runs — RealCheeseLord, Aug 22 '17 at 13:46
The aforementioned duplicate is not really a duplicate. The OP in this case is referencing the same random number multiple times in the `SELECT`. This makes a difference. — Gordon Linoff, Aug 22 '17 at 13:47
select case when rand_value < 0.23 then ..... from (select rand() as rand_value) — ventik, Aug 22 '17 at 13:48
@ventik - unfortunately, T-SQL offers no guarantees over how many times it will evaluate a function/expression vs how many times that function/expression textually appears in the query. — Damien_The_Unbeliever, Aug 22 '17 at 13:49
@Damien_The_Unbeliever I dont' understand what you mean. You mean that there is a possibility that query "select rand_value as r1, rand_value as r2 from (select rand() as rand_value)" returns row with different r1 and r2 values? — ventik, Aug 22 '17 at 13:55
@ventik - it's certainly *possible*. It's difficult to deliberately provoke this behaviour. I'm just warning that there is very little that T-SQL *guarantees* about the evaluation of expressions. — Damien_The_Unbeliever, Aug 22 '17 at 14:00
@Damien_The_Unbeliever as far as I understand different values can occur only if you make calculations with rand_value and only the results of these calculations may differ. Am I right? If you compare pure rand_values thye will be the same — ventik, Aug 22 '17 at 14:12
You should do this in the application layer... or if you insist - create a CLR function for this. Databases are for storing data - not generating random numbers — Milney, Aug 22 '17 at 14:42

Gordon Linoff · Accepted Answer · 2017-08-23T02:45:48.223

4

You can. However, rand() is evaluated only once per query, not once per row. Instead, you want to do something with newid(). However, because you reference the value multiple times in the CASE, this poses a challenge. One method is:

SELECT . . .
        (CASE WHEN rnd < 0.23 THEN 'Win'
              WHEN rnd < 0.89 THEN 'Lose'
              WHEN rnd >= 0.89 THEN 'Jackpot'
              ELSE 'Other' -- impossible
         END) as [outcome]
FROM (SELECT t.*, rand(convert(varbinary, newid())) as rnd
      FROM t
     ) t

edited Aug 23 '17 at 02:45

answered Aug 22 '17 at 13:51

Gordon Linoff

1,242,037
58
646
786

2

I think you mean "`rand()` is NOT evaluated only once per row", otherwise the original query would work. – D Stanley Aug 22 '17 at 13:57
@DStanley . . . I meant "once per query". Thank you for the clarification. – Gordon Linoff Aug 23 '17 at 02:46

score 0 · Answer 2 · answered Aug 22 '17 at 13:47

0

Each time you call RAND(), a random seed is given since you didn't specify one. Just give RAND() a seed and it will remain the same.

select 
    case when 1 = 1 then rand(4) end
    ,case when 2=2 then rand(4) end

Or use a column value...

select 
    case when 1 = 1 then rand(someColumn) end
    ,case when 2=2 then rand(someColumn) end

answered Aug 22 '17 at 13:47

S3S

24,809
5
26
45

Won't this mean that every row has the same outcome? – tomdemaine Aug 22 '17 at 13:51
Yes, if you give it a seed and no, if you use a column which is unique to the row. – S3S Aug 22 '17 at 14:06

score 0 · Answer 3 · answered Aug 22 '17 at 13:52

Another option is with a Cross Apply

Example

Select [Player name]
      ,[Stake]
      ,[current jackpot]
      ,[OutCome] = case when b.randV<0.23 then 'win'
                        when b.randV between 0.23 and 0.89 then 'lose'
                        when b.randV>=0.89 then 'Jackpot'
                        else 'other' end
 From  YourTable
 Cross Apply (values (rand(cast( NewID() as varbinary )))) B(randV)

Can you use random numbers in a CASE statement (SQL Server 2016)

3 Answers3