Today, I have come through
a post by one of my good friends and one of the most famous SQL Server experts in India –
Madhivanan.
The question was : “Given a number N, write a code to print all positive numbers less than N in which all adjacent digits differ by 1”
Here is my attempt to solve this using SQL Server T-SQL
--Provide input value
declare @number int
set @number=105
--Create a temporary table to store numbers
Drop table if exists #temp
create table #temp (number int)
;with ctebuilder as(
select
row_number () over (order by (select null)) as sno
from
sys.sysobjects as t1 cross join sys.sysobjects as t2 )
Insert into #temp(number) Select sno From ctebuilder where sno<=@number
;with cte as(
select
t1.number number,
cast(substring(cast(t1.number as varchar(20)),t2.number,1) as int) as number2,
row_number () over(partition by t1.number order by (select null) asc) Rn
from #temp as t1 cross join #temp as t2
where t2.number <= len(t1.number) and t1.number <=@number
)
Select number From(
Select * ,LAG(number2,1) OVER (partition by number
ORDER BY (select NULL) asc
) previous_val From cte
) A group by number
having count(case when previous_Val - number2 in (1,-1) then 1 Else NULL End )
= count(number2)-1
Like this:
Like Loading...
Related
SQLZealots 
Very nice post Latheesh. Thank you so much
LikeLike