Generating Narcissistic Number or Armstrong Number and TSQL

Transact-SQL
1

https://mathworld.wolfram.com/NarcissisticNumber.html#:~:text=th%20powers%20of%20their%20digits%20(a%20finite%20sequence)%20are%20called,9474%2C%2054748%2C%20...

I am fan of numbers, so obsessed that in my teen age i found , on my own, how to fill a magic square of odd length.
I am fan of TSQL and prefer writing tsql over c# or other coding language.

This is not my homework nor my company work.
This is just for fun.

I love Narcissistic Number so much so that my cell# is 0331-9307774
(where 0331 is service provider number
and there is no service provider whoose number starts with 467)

4679307774 is the only Narcissistic Number in 10 digits length.

I am trying to find this number with TSQL.
but the tsql has compilation error

the error is
Arithmetic overflow error for type int, value = 3486784401.000000.

What I want
a) remove the compilation / syntax error.
b) suggestion as how to produce armstrong number in TSQL as fast as possible.

here is the code with syntax error
Arithmetic overflow error for type int, value = 3486784401.000000.

select Number=a*1000000000 + b*100000000 + c*10000000 + d*1000000 + e*100000 + f*10000 + g*1000 + h*100 + i*10+ j
from
(select a=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)a
cross join
(select b=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)b
cross join
(select c=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)c
cross join
(select d=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)d
cross join
(select e=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)e
cross join
(select f=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)f
cross join
(select g=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)g
cross join
(select h=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)h
cross join
(select i=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)i
cross join
(select j=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)j
where    a*1000000000 + b*100000000 + c*10000000 + d*1000000 + e*100000 + f*10000 + g*1000 + h*100+ i*10+ j
       = power(a,10) + power(b,10) + power(c,10) + power(d,10) + power(e,10) + power(f,10) + power(g,10) + power(h,10) + power(i,10) + power(j,10)
and   (a*1000000000 + b*100000000 + c*10000000 + d*1000000 + e*100000 + f*10000 + g*1000 + h*100 + i*10+ j)> 1000000000
order by 1

this code produces 1st 15 armstrong numbers

select Number=a*100 + b*10 + c
from
(select a=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)a
cross join
(select b=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)b
cross join
(select c=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)c
where a*100 + b*10 + c  = power(a,3) + power(b,3) + power(c,3)
and   (a*100 + b*10 + c )> 100

union all
select Number=a*1000 + b*100 + c*10 + d
from
(select a=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)a
cross join
(select b=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)b
cross join
(select c=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)c
cross join
(select d=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)d
where a*1000 + b*100 + c*10 + d  = power(a,4) + power(b,4) + power(c,4) + power(d,4)
and   (a*1000 + b*100 + c*10 + d)> 1000

union all
select Number=a*10000 + b*1000 + c*100 + d*10 + e
from
(select a=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)a
cross join
(select b=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)b
cross join
(select c=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)c
cross join
(select d=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)d
cross join
(select e=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)e
where a*10000 + b*1000 + c*100 + d*10 + e  = power(a,5) + power(b,5) + power(c,5) + power(d,5) + power(e,5)
and   (a*10000 + b*1000 + c*100 + d*10 + e)> 10000

union all
select Number=a*100000 + b*10000 + c*1000 + d*100 + e*10 + f
from
(select a=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)a
cross join
(select b=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)b
cross join
(select c=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)c
cross join
(select d=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)d
cross join
(select e=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)e
cross join
(select f=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)f
where    a*100000 + b*10000 + c*1000 + d*100 + e*10 + f  
       = power(a,6) + power(b,6) + power(c,6) + power(d,6) + power(e,6) + power(f,6)
and   (a*100000 + b*10000 + c*1000 + d*100 + e*10 + f)> 100000


union all
select Number=a*1000000 + b*100000 + c*10000 + d*1000 + e*100 + f*10 + g
from
(select a=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)a
cross join
(select b=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)b
cross join
(select c=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)c
cross join
(select d=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)d
cross join
(select e=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)e
cross join
(select f=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)f
cross join
(select g=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)g
where    a*1000000 + b*100000 + c*10000 + d*1000 + e*100 + f*10 + g
       = power(a,7) + power(b,7) + power(c,7) + power(d,7) + power(e,7) + power(f,7) + power(g,7)
and   (a*1000000 + b*100000 + c*10000 + d*1000 + e*100 + f*10 + g)> 1000000
order by 1

Number
153
370
371
407
1634
8208
9474
54748
92727
93084
548834
1741725
4210818
9800817
9926315

this code produces the three possible numbers in length 8

select Number=a*10000000 + b*1000000 + c*100000 + d*10000 + e*1000 + f*100 + g*10 + h
from
(select a=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)a
cross join
(select b=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)b
cross join
(select c=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)c
cross join
(select d=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)d
cross join
(select e=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)e
cross join
(select f=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)f
cross join
(select g=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)g
cross join
(select h=0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)h
where    a*10000000 + b*1000000 + c*100000 + d*10000 + e*1000 + f*100 + g*10 + h
       = power(a,8) + power(b,8) + power(c,8) + power(d,8) + power(e,8) + power(f,8) + power(g,8) + power(h,8)
and   (a*10000000 + b*1000000 + c*100000 + d*10000 + e*1000 + f*100 + g*10 + h)> 10000000
order by 1

Number
24678050
24678051
88593477

2

Do you want the zero numbers in your data set? this is just the sampling of a and b

image

3

also maybe you might want to clean things up so we don't get strabismus looking at your data sample. Cleanliness is next to Godliness. If you sampling is busy and cluttered, very hard to "see" the answer

;with cteone_to_ten
as
(
select distinct column_id from sys.all_columns where column_id between 1 and 9
)
select *
from
(
	select a=0 union select * from cteone_to_ten
)a
cross join
(
select b=0 union select * from cteone_to_ten
)b
4

yes.

5

Yes.
Got idea.

A cte for 1 and 9 and then to use the cte simplify the code.

6

@yosiasz

Code simplified.

Arithmatic overflow error removed.

The query is running...
lets see how much time it takes to produce the only possible number in 10 digit lengh

;with cteone_to_ten
as
(
select numbers= convert(bigint,0) union select distinct column_id from sys.all_columns where column_id between 1 and 9
)
select Number=a*1000000000 + b*100000000 + c*10000000 + d*1000000 + e*100000 + f*10000 + g*1000 + h*100 + i*10+ j
from
(
	select A=numbers from cteone_to_ten
)a
cross join
(
	select b=numbers from cteone_to_ten
)b
cross join
(
	select c=numbers from cteone_to_ten
)c
cross join
(
	select d=numbers from cteone_to_ten
)d
cross join
(
	select e=numbers from cteone_to_ten
)e
cross join
(
	select f=numbers from cteone_to_ten
)f
cross join
(
	select g=numbers from cteone_to_ten
)g
cross join
(
	select h=numbers from cteone_to_ten
)h
cross join
(
	select i=numbers from cteone_to_ten
)i
cross join
(
	select j=numbers from cteone_to_ten
)j
where    a*1000000000 + b*100000000 + c*10000000 + d*1000000 
         + e*100000 + f*10000 + g*1000 + h*100+ i*10+ j
       = power(a,10) + power(b,10) + power(c,10) + power(d,10) + power(e,10) 
       + power(f,10) + power(g,10) + power(h,10) + power(i,10) + power(j,10)
and   (a*1000000000 + b*100000000 + c*10000000 + d*1000000 
       + e*100000 + f*10000 + g*1000 + h*100 + i*10+ j)> 1000000000
7

you might also simplify things more in order to avoid damage to our eyes :wink:

select Number=a*a.mult + b*b.mult + c*c.mult + d*d.mult + e*100000 + f*10000 + g*1000 + h*100 + i*10+ j
from
(
	select a=numbers, 1000000000 mult from cteone_to_ten
)a
cross join
(
	select b=numbers, 100000000 mult from cteone_to_ten
)b
8

Further beautified.
Since the 10 digit version is still running (6 hours till now),
i am pasting here 7 digit version
which gives the result in 17 secs.


;with cte
as
(
select Number= convert(bigint,0) 
       union 
       select distinct column_id 
       from sys.all_columns 
       where column_id between 1 and 9
)
select armstrong_number= num_a + num_b + num_c + num_d + num_e + num_f + num_g
from
(
	select Num_a=number *1000000 ,pwr_a= power(number,7)from cte
)a
cross join
(
	select Num_b=number *100000 ,pwr_b= power(number,7)from cte
)b
cross join
(
	select Num_c=number *10000 ,pwr_c=power(number,7)from cte
)c
cross join
(
	select Num_d= number *1000 ,pwr_d=power(number,7)from cte
)d
cross join
(
	select Num_e= number*100, pwr_e=power(number,7)from cte
)e
cross join
(
	select Num_f= number*10 ,pwr_f= power(number,7)from cte
)f
cross join
(
	select Num_g=number ,pwr_g= power(number,7)from cte
)g

where    num_a + num_b + num_c + num_d + num_e + num_f + num_g
       = pwr_a + pwr_b + pwr_c + pwr_d + pwr_e + pwr_f + pwr_g 
   and num_a + num_b + num_c + num_d + num_e + num_f + num_g > 999999
            
 order by 1

Result

1741725
4210818
9800817
9926315

Right now, googling how to generate billion numbers fastest.

9

In 8 digits,
it took 4 minutes to find the three possible armstrong numbers


;with cte
as
(
select Number= convert(bigint,0) 
       union 
       select distinct column_id 
       from sys.all_columns 
       where column_id between 1 and 9
)
select armstrong_number= num_a + num_b + num_c + num_d + num_e + num_f + num_g + num_h
from
(
	select Num_a=number *10000000 ,pwr_a= power(number,8)from cte
)a
cross join
(
	select Num_b=number *1000000 ,pwr_b= power(number,8)from cte
)b
cross join
(
	select Num_c=number *100000 ,pwr_c=power(number,8)from cte
)c
cross join
(
	select Num_d= number *10000 ,pwr_d=power(number,8)from cte
)d
cross join
(
	select Num_e= number*1000, pwr_e=power(number,8)from cte
)e
cross join
(
	select Num_f= number*100 ,pwr_f= power(number,8)from cte
)f
cross join
(
	select Num_g=number*10 ,pwr_g= power(number,8)from cte
)g

cross join
(
	select Num_h=number ,pwr_h= power(number,8)from cte
)h

where    num_a + num_b + num_c + num_d + num_e + num_f + num_g + num_h
       = pwr_a + pwr_b + pwr_c + pwr_d + pwr_e + pwr_f + pwr_g + pwr_h
   and num_a + num_b + num_c + num_d + num_e + num_f + num_g + num_h > 9999999
       
       
 order by 1

result

24678050
24678051
88593477

10

10 digit number, took 23 minutes on my machine:

IF OBJECT_ID('tempdb..#work') IS NOT NULL
    DROP TABLE #work;
GO
CREATE TABLE #work
(
    n bigint  not null PRIMARY KEY CLUSTERED,
    nn bigint not null
);

;WITH cte
AS
(
SELECT N,
       CONVERT(bigint,power(N,10)) NN
  FROM (VALUES (CONVERT(bigint,0)),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(N)
)
INSERT INTO #work(n,nn)
SELECT n,nn
  FROM cte;

SELECT concat(j.N,i.N,h.N,g.N,f.N,e.N,d.N,c.N,b.N,a.N) NarcissisticNumber
  FROM #work a, #work b, #work c, #work d, #work e, #work f, #work g, #work h, #work i, #work j
 WHERE a.nn+b.nn+c.nn+d.nn+e.nn+f.nn+g.nn+h.nn+i.nn+j.nn=a.N+10*(b.N+10*(c.N+10*(d.N+10*(e.N+10*(f.N+10*(g.N+10*(h.N+10*(i.N+10*j.N))))))))
   AND j.N <> 0 -- Prevent leading zero numbers
 ORDER BY 1;
2 Likes
11

use three ticks in front and end of code here is a sample tick for you --> `

2 Likes
12

Thank you yosiasz
I've amended the post.

1 Like
13

Thank you very much Jonathan AC Roberts.

In my slow machine it took 45 minutes.

My code was taking 8 hours.

Thanks for accepting my request and sharing your best of knowlege

Very best regards.

1 Like
14

<<This is the first time jacroberts has posted — let’s welcome them to our community!>>

I am inspired by his great posts.

1 Like
15

Generating 10 temp tables in one go is new to me.

Thanks

16

Here is the print screen of my slow machine and
jacroberts fast code result

jacroberts
jacroberts1157×428 51 KB