user1781272 - 1 year ago 45

SQL Question

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.

Source (Stackoverflow)