Home Forums SQL Server 2008 SQL Server 2008 - General Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed) RE: Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed)

  • Also, you don't need to check if the same digits from different joins are not equal (eg. if checked a.num<>b.num in one "join" you don't need to check it again in other "joins"). Therefore all JOINs can be rewritten using "incremented" NOT IN:

    with a(num) as

    (select 1 union select 2 union select 5 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 c.num NOT IN (a.num, b.num)

    join a as d on d.num NOT IN (a.num, b.num, c.num)

    join a as e on e.num NOT IN (a.num, b.num, c.num, d.num)

    join a as f on f.num NOT IN (a.num, b.num, c.num, d.num, e.num)

    )

    select distinct *, num1-num2 from b

    where num1-num2 = 33333

    order by 1,2

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]