darrrrUC darrrrUC - 4 months ago 16
SQL Question

random between variables

Lets say I have 3 variables in sql:

var1
,
var2
and
var3


Can I choose on of these by random?
I do not want to have a range set by variable, I just need the random function to pick one of my variables.

For example:

DECLARE @First INT
DECLARE @Second INT
DECLARE @Third INT

SET @First = 1
SET @Second = 2
SET @Third = 3


And I would get a random value: 1,2 or 3

Thanks

Answer

Based on @TobiasR's Idea of ordering and Limiting i made this:

declare @var1 nvarchar(max)
declare @var2 nvarchar(max)
declare @var3 nvarchar(max)

set @var1 = 'X'
set @var2 = 'Y'
set @var3 = 'Z'

x.* into #temp 
from (
select @var1 as var , rand() as rand
union 
select @var2, rand() 
union 
select @var3, rand()
) x

select top 1 * from #temp 
order by rand

drop table #temp

The basic Idea is assigning every variable a random number and then selecting the one with the lowest assigned value.

Or without the use of a temporary Table by using NEWID() as sorting value: (credits to @ughai)

SELECT TOP 1 * FROM (VALUES(@var1),(@var2),(@var3)) as T(a) ORDER BY NEWID()
Comments