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

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hefterr

ASKER

Thanks,
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()

Jim Horn

Good deal. Btw, save you some typing...

INSERT INTO @table ( value ) VALUES (1), (2), (3), (4), (5)