SQL Puzzle: SQL Advance Query – If column forms a sequence then print ‘Yes’ otherwise ‘No’

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input & output data, and if column forms a sequence then print ‘Yes’ other print ‘No’.

First input data in sequence:

-----------

100

101

102

103

104

105

Require Output:

Flag

----

Yes

Create a table with sample data:

create table rno (n int)

insert into rno values

(100),(101),(102),(103),(104),(105)

Solution:

with cteSeq as

(

select

,ROW_NUMBER() over (order by n) as rnk

from rno

)

,cteCnt as

(

select

n-rnk as res

,COUNT(1) as cnt

from cteSeq

group by (n-rnk)

)

select

case when count(1) = 1 then 'Yes' else 'No' end as Flag

from cteCnt

Jul 13, 2017Anvesh Patel

Database Engineer