RE: Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed)

Eugene Elutin

SSC Guru

Points: 59322

September 12, 2012 at 7:43 am

#1536712

You don't really need last join as the last digit of the second number can only be '3'

with a(num) as

(select 1 union select 2 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)

,b(num1,num2) as

(

select

convert(bigint, '4' + convert(varchar,a.num) + convert(varchar,b.num) + convert(varchar,c.num) + '6')

,convert(bigint, convert(varchar,d.num) + convert(varchar,e.num) + convert(varchar,f.num) + '3')

from

a

join a as b on a.num <> b.num

join a as c on a.num <> b.num and a.num <> c.num

and b.num <> c.num

join a as d on a.num <> b.num and a.num <> c.num and a.num <> d.num

and b.num <> c.num and b.num <> d.num

and c.num <> d.num

join a as e on a.num <> b.num and a.num <> c.num and a.num <> d.num and a.num <> e.num

and b.num <> c.num and b.num <> d.num and b.num <> e.num

and c.num <> d.num and c.num <> e.num

and d.num <> e.num

join a as f on a.num <> b.num and a.num <> c.num and a.num <> d.num and a.num <> e.num and a.num <> f.num

and b.num <> c.num and b.num <> d.num and b.num <> e.num and b.num <> f.num

and c.num <> d.num and c.num <> e.num and c.num <> f.num

and d.num <> e.num and d.num <> f.num

and e.num <> f.num

)

select distinct *, num1-num2 from b

where num1-num2 = 33333

order by 1,2

Also, I thought that you shouldn't be able to use 4 and 6 as they were already used by the first number. In this case you will get only single possible result: