Search for the occurrence of a list of values

Question

I'm trying to find an optimized way to identify if a specific set of values exists in a list.

For example, lets assume the following list of records in a table

Id          Value
1           A
2           B
3           A
4           C
5           A
6           B
7           C
8           C
9           A

I'm trying to find a way to check how much times the sequence {A, B} or {A, B, C} occurs, for example.

I know I can do this with cursors but I was checking if there's any other option that would be preferable in terms of performance.

The result I'd expect would by something like this:

{A, B}: 2 times:
{A, B, C}: 1 time.

I'm using Sql Server.

Answer 1

Probably the simplest way is to use the ANSI standard functions lag() and/or lead():

select count(*)
from (select t.*,
             lead(value) over (order by id) as next_value,
             lead(value, 2) over (order by id) as next_value2,
      from t
     ) t
where value = 'A' and next_value = 'B' and next_value2 = 'C';