hefterr
asked on
Generating Random Number List
Hi Experts,
I have a one time programing request that I think could be solved with some SQL (also coding in ColdFusion)
Problem: I need to generate in a random sequence a list if all possible 4 digit numbers only using the set of digits (1,2,3,4,5)
ex: 1234, 4455, 5555, 1111.
I'm not sure how to do this best. I could
- Sequentially insert rows with all numbers from 1111 - 5555 (within the set of digits)
- Assign with it a random number (or use SQL to generate that).
- Select all rows ordered by random number?
What do you think?
Thanks in advance
hefterr
I have a one time programing request that I think could be solved with some SQL (also coding in ColdFusion)
Problem: I need to generate in a random sequence a list if all possible 4 digit numbers only using the set of digits (1,2,3,4,5)
ex: 1234, 4455, 5555, 1111.
I'm not sure how to do this best. I could
- Sequentially insert rows with all numbers from 1111 - 5555 (within the set of digits)
- Assign with it a random number (or use SQL to generate that).
- Select all rows ordered by random number?
What do you think?
Thanks in advance
hefterr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good deal. Btw, save you some typing...
INSERT INTO @table ( value ) VALUES (1), (2), (3), (4), (5)
INSERT INTO @table ( value ) VALUES (1), (2), (3), (4), (5)
ASKER
I ended up with something I hacked out as it was a 1 time deal for 625 numbers
DECLARE @table TABLE ( value char(1) )
INSERT INTO @table ( value ) VALUES (1)
INSERT INTO @table ( value ) VALUES (2)
INSERT INTO @table ( value ) VALUES (3)
INSERT INTO @table ( value ) VALUES (4)
INSERT INTO @table ( value ) VALUES (5)
SELECT t1.value +''+ t2.value +''+ t3.value + '' + t4.value AS theNumber
FROM @table t1
CROSS JOIN @table t2
CROSS JOIN @table t3
CROSS JOIN @table t4
order by newid()