GENERATING A RANDOM NUMBER FIELD 1

[pagey47]

How do I populate a field with a randomly generated number on every row???

I am trying to sort a long list of sequential phone numbers in to a randomised order. I am currently using left and right queries to take two numbers from the phone number and divide them by eachother, but there are still sequential patterns in the numbers.

Thanks

 

[hmckillop]

SI your requirement not to have sequential numbers or to randomise them. By the nature of random it means you could theoretically end up with a complete sequential list of numbers (albeit an extreme chance).

For what purpose are you trying to randomise them, i.e. are you re-storing them in another table, or are you just looking for a resultset of random numbers?

If I can understand more what and why you are achieving it might be easier to find a solution which best fits. What size of number do you require?

In answer to your first question you can get randomise number by using the following function:

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )



"I'm living so far beyond my income that we may almost be said to be living apart

 

[pagey47]

Thanks for your response!

I basically want to extract data from the table via the TOPN query with an order by but the numbers are sequential and I want a random selection. I have tried your suggestion but it seems to copy the random number on every row.

 

[vongrunt]

Unless I missed something...

Randomised order = ORDER BY NEWID()



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[hmckillop]

That was what I was going to propose next, but I wanted to be clear what was required first, have been making a habit of jumping the gun lately.
Anyway very useful deserves a star.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[pagey47]

Spot on, Perfect, thanks very much!!!