CODEWITHSUNDEEP
sqlarrayssql-server-2008random
Lou
Lou

Reputation: 4474

Fixed array and random numbers in an SQL query

What's the easiest way to select one of five fixed strings returned in an SQL Server query, randomly?

I.e. the equivalent of:

function randomColumn() {
   var values = ['apple', 'banana', 'orange', 'cherry', 'lemon'];
   var idx = Math.floor(Math.random() * 5);
   return values[idx];
}

I need to change my existing SQL script to have a certain column return one of these values, without the need to change my client code.

Do I need to create a temp table?

I'm using SQL Server 2008 R2.

Upvotes: 0

Views: 4041

Answers (4)

Ariful Haque
Ariful Haque

Reputation: 888

DECLARE @ListofIDs TABLE(IDs VARCHAR(100), ID INT IDENTITY(1,1));
INSERT INTO @ListofIDs
SELECT 'a'
UNION ALL
SELECT 'b'
UNION ALL
SELECT '20'
UNION ALL
SELECT 'c'
UNION ALL
SELECT '30'
UNION ALL
SELECT 'd';
SELECT * FROM @ListofIDs;

SELECT  Ids from @ListofIDs where ID=1+ CONVERT(INT, (5)*RAND())

Upvotes: 0

Ruzbeh Irani
Ruzbeh Irani

Reputation: 2438

Select 'apple' values 
INTO #tmp
UNION ALL 
Select'banana' values 
UNION ALL 
Select 'orange' values      
.
.
.  
select top 1 values 
from #tmp
order by newid()

OR

ALTER TABLE #tmp
ADD id BIGINT IDENTITY(1,1)
DECLARE @rand BIGINT 

SET @rand=rand()*4

select top 1 values 
from #tmp
where id=@rand

Upvotes: 1

Guffa
Guffa

Reputation: 700262

You don't need a temporary table for a few strings, you can create the result on the fly:

select str
from (
  select 0 as id, 'apple' as str
  union all
  select 1, 'banana'
  union all
  select 2, 'orange'
  union all
  select 3, 'cherry'
  union all
  select 4, 'lemon'
) x
where id = floor(rand() * 5)

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

select top 1 Value
from Table1
order by newid()

SQL Fiddle Example

Upvotes: 3

Related Questions