user1781272 user1781272 - 5 months ago 16
SQL Question

SQL - Select first n row where next n Row values are sequential + 1

I have a table with row ID and Cct numbers. The Cct column can be +1 sequential depending on the preceeding select statement.

I want to find the first row where the next n rows cct no. increments + 1 to n-1.

Sample

ID Cct
1 1
2 3
7 5
8 6
10 9
11 10
12 11


if n = 2 I want to select row with ID 7 because it is the first time that the next n rows cct value are value + 1

if n = 3 I want to select row with ID 10 as the next n-1 rows are incremented by + 1

Hope this is clear

Answer

I think I get it. You are looking for lengths of sequences in the data, where a sequence is incremented by 1.

You can identify them using row_number() and subtraction -- the difference is fixed for a sequence.

So:

select top 1 min(id), count(*) as length
from (select s.*, (cct - row_number() over (order by id)) as grp
      from sample s
     ) s
group by grp
having count(*) >= @n
order by min(id)

This gets the first time that a sequence of length (at least) @n appears in the data.