Select random element from list.

[HestonJames]

Hello Guys,

I've got a query which looks something like:

Insert Into MyTable (
                     Something,
                     Another,
                     OneMore
                     )
Values (
        100,
        [b]Name[/b],
        300
        )

Now what I want to do is insert a random value from a given list into the 'another' column, so lets say I have a list which looks as follows.

'bob', 'dave', 'george', 'me', 'you', 'michael jackson'

Each time I run the insert query I want it to select one of those names at ransom to use as the value to insert!

I'd appreciate your suggestions, does SQL have any nice built in functions to do this for me? like ListRand() or something like that?

Many thanks,

Heston

 

[simian101]

Assign a id number to each name

1, 'bob'
2, 'dave'
3, 'george'
4, 'me'
5, 'you'
6, 'michael jackson'
etc.

Then

select *
from list
where id = (cast(RAND() * 6 as int) +1)

Then use that name to insert into the the other table.

Simi

 

[simian101]

Where 6 = number of names in the list.

Simi

 

[bborissov]

DECLARE @List TABLE (Name varchar(20))
INSERT INTO @List VALUES ('bob')
INSERT INTO @List VALUES ('dave')
INSERT INTO @List VALUES ('george')
INSERT INTO @List VALUES ('me')
INSERT INTO @List VALUES ('you')
INSERT INTO @List VALUES ('michael jackson')


INSERT INTO MyTable (Something,
                     Another,
                     OneMore)
SELECT TOP 1   100,
               Name,
               300
FROM @List
ORDER BY NEWID()

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

It's better to:


select top 1 * From table order by NewId()



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hello Chaps,

Very good work, this certainly seems to be along the right tracks!! It works in the use case defined above perfectly, however, I've just tried to use the same principle in a different use case where its a subquery and it's not quite so happy ;-)

DECLARE @Media_IDs TABLE (
						Media_ID int
						)
/* Define the media_id's to be used in the copy. */
INSERT INTO @Media_IDs Values (10)
INSERT INTO @Media_IDs Values (20)

INSERT INTO MessageLog
		(
		Device_ID,
		LogDateTime,
		LogClass_ID,
		Media_ID,
		Campaign_ID,
		ThinkTank_ID
		)
SELECT	Device_ID,
		DATEADD(m, 3, LogDateTime),
		LogClass_ID,
		/* Select a random media_id to use */
		(Select Top 1 Media_ID From @Media_IDs Order By NewID()),
		Campaign_ID,
		10 /* Pod To Copy To */
FROM	MessageLog
WHERE	ThinkTank_ID = 41 /* Pod To Copy From */
AND		LogDateTime >= '20081104 00:00:00'
AND		LogDateTime  < '20081106 00:00:00'

This query runs however, I get the same randomly selected ID for every row which is inserted by the query, it seems to select the random entry but then use that for every single row.

Any suggestions on how to implement the random selector on a use case like this?

Cheers my dears,

Heston