Inserting random order of numbers between defined range

Question

Inserting random order of numbers between defined range

Mark Green-451799

SSC-Addicted

Points: 490
More actions
November 6, 2007 at 6:21 am

#70993

DECLARE @Number_of_government_regions INT
SET @Number_of_government_regions = (SELECT 354)
CREATE TABLE Random_region_lookup_table (
Entry_key INT IDENTITY,
Generation_number int NOT NULL,
Place_key int NOT NULL
)
I want to assign the government regions a random order in this lookup table. I want to do this 10,000 times. Therefore I will have 10,000 generations and each generation will have 354 rows in. Each row shows an element tagged any number between 1 and 354 inclusive. Each government region is represented only once in a generation.
Please can someone show me a bit of T-SQL that will help me do this?

Viewing 15 posts - 1 through 15 (of 19 total)

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

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

Are the random numbers allowed to repeat within a generation and are others allowed to be missing as would a true random number provide?

--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)

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

Mark

This isn't tested. It requires you to create a numbers (or tally) table called MyNumbers, with values up to at least 10,000. There's lots of discussion on this site about how to do that. It also requires you to add a column for the region name into your table, and to make the Place_key column nullable. It assumes, of course, that you have the names of all the regions already stored in a table called Regions.

INSERT INTO Random_region_lookup_table

SELECT n.MyNumber, r.RegionName

FROM MyNumbers n CROSS JOIN Regions r

ORDER BY MyNumber, NEWID()

UPDATE Random_region_lookup_table

SET Place_key = Entry_key%354

John

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

Mark Green-451799

SSC-Addicted

Points: 490

November 6, 2007 at 6:42 am

#748626

No the number can not be repeated

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

Since the numbers you want are 1 to 354 and not 0 to 353, you'll probably have to change John's code just a bit...

UPDATE Random_region_lookup_table

SET Place_key = Entry_key%354

... should probably be...

UPDATE Random_region_lookup_table

SET Place_key = (Entry_key%354)+1

To make the Tally table John spoke of (he call's his "MyNumbers"... I call mine just "Tally"), use the following code (setup to create the Tally table as "MyNumbers")...

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.MyNumbers

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.MyNumbers

ADD CONSTRAINT PK_MyNumbers_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.MyNumbers TO PUBLIC

--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)

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

...or maybe even

UPDATE Random_region_lookup_table

SET Place_key = CASE WHEN Entry_key%354 = 0 THEN 354

ELSE Entry_key%354 END

or

UPDATE Random_region_lookup_table

SET Place_key = (Entry_key - 1)%354 + 1

Either that or seed your identity at 0 instead of 1 and use your code. Which leads on to a serious point - this could all break if you don't reseed your identity column before you start (or if the identity propery is set to something other than (1,1)).

Either way, hopefully now Mark can see what we're trying to do and can tailor it to his exact rquirements.

John

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

I don't really understand why you need a table with values up to at least 10,000. I want 1 to 354 to be reordered 10,000 times. Each time this reordering happens I want the order recording. I'll take another look at your code. However, I just want to make sure that you understand what I want.

Update: I think that I understand what you are doing now. I didn't initially click that you were using the modus command.

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

Mark

It's to save you having to use a cursor or loop. Row-by-row processing is nearly always less efficient than set-based processing in T-SQL, and the numbers table allows you to take the set-based approach. Try running the code and see if it gives you what you need.

Edit: actually you've drawn my attention to a flaw in the code when you reminded me that I said "at least". You either need to make sure there's exactly 10000 numbers in the table, or put a where clause in that first query:

WHERE n.MyNumber <= 10000

John

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

In case you happened to have a 2005 Server available - here's the "new" version.

insert Random_region_lookup_table(generation_number,entry_key,,place_key)

select gens.n Generation,regions.n region,Row_number() OVER (Partition by m.n order by newid()) ranks from mynumbers gens, mynumbers regions

where regions.n<355 and gens.n<10001

----------------------------------------------------------------------------------
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?

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

Kewl! Anyone interested in a million row duration test on this? 😀 I'd need some help 'cause I don't have 2k5...

--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)

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

Will the following give me the tally table that I need then?

--===== Create and populate the Tally table on the fly

SELECTTOP 10000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.MyNumbers

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.MyNumbers

ADD CONSTRAINT PK_MyNumbers_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.MyNumbers TO PUBLIC

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

Jeff Moden (11/6/2007)
Kewl! Anyone interested in a million row duration test on this? 😀 I'd need some help 'cause I don't have 2k5...

Well - the test was for 3,540,000 records, and it ran in 34 secs...

----------------------------------------------------------------------------------
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?

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

Mark Green (11/6/2007)
Will the following give me the tally table that I need then?

Yessir - it would.

----------------------------------------------------------------------------------
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?

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

Matt Miller (11/6/2007)
Well - the test was for 3,540,000 records, and it ran in 34 secs...

Perfect... I'm at work right now... I'll try to set something up tonight... thanks, Matt!

--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)

Lowell SSC Guru Points: 323518 More actions · Answer 14

Jeff Moden (11/6/2007)
Kewl! Anyone interested in a million row duration test on this? 😀 I'd need some help 'cause I don't have 2k5...

Jeff I feel really bad you don't have 2005 yet; I'd be glad to sell you a discounted copy of the free version of 2005 Developer Express. you can save thousands by getting it from me!

get the SQL Server 2005 Express Edition with Advanced Services SP1 version here: http://articles.techrepublic.com.com/5100-9592-6102265.html

you can send me a check at your convenience 🙂

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!