Inserting random order of numbers between defined range

Question

Inserting random order of numbers between defined range

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Mark Green-451799 SSC-Addicted Points: 490 More actions · Answer 1

This code does not give me a random order of numbers. It just gives me them in numerical order. Albeit 10,000 times.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Mark Green (11/6/2007)
This code does not give me a random order of numbers. It just gives me them in numerical order. Albeit 10,000 times.

I'm not sure we're using the table in the same way. Mine (and I think John's as well) uses the fields this way:

Generation_number = the generation

entry_key = the region to process

place_key = the order (within the generation) in which to "process" the generation.

So -

Select generation_number, place_key, entry_key from Random_region_lookup_table

order by generation_number, place_key

should give you a different ordering of the entry_keys (regions) for every generation.

Sorry - I was wondering if we were using the same fields in the same way.....Let me know how far off we are from what you need.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Matt Miller (11/6/2007)
Mark Green (11/6/2007)
This code does not give me a random order of numbers. It just gives me them in numerical order. Albeit 10,000 times.
I'm not sure we're using the table in the same way. Mine (and I think John's as well) uses the fields this way:
Generation_number = the generation
entry_key = the region to process
place_key = the order (within the generation) in which to "process" the generation.

Actually, I think mine was a little different. Entry_key was a unique row identifier, generation_number is the generation number (between 1 and 10000) and place_key was the order within the generation. This was why I said you needed to add another column for the name of the region (when you do it my way). Mark, even if this isn't exactly what you asked for, you should be able to tweak it to fit your exact requirements?

John

Mark Green-451799 SSC-Addicted Points: 490 More actions · Answer 4

SELECT *

FROM my_table

ORDER BY RAND()

I think that I need to look for a solution that uses the RAND function. But that raises the question whether RAND is really RAND

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

The answer is, no... Within a given SELECT, Rand is not RAND unless it has a random seed... the only thing in SQL Server that even comes close to being random is NEWID()... and it's very random...

So, this will work just exactly like you'd like it to...

SELECT *

FROM my_table

ORDER BY NEWID()

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)