CODEWITHSUNDEEP
sql.netsql-servergaps-and-islands
sonarforte
sonarforte

Reputation: 1624

SQL Server 2008: find number of contiguous rows with equal values

I have a table with multiple Ids. Each Id has values arranged by a sequential index.

create table myValues
(
  id  int,
  ind int,
  val int
)

insert into myValues
values
(21, 5, 300),
(21, 4, 310),
(21, 3, 300),
(21, 2, 300),
(21, 1, 345),
(21, 0, 300),
(22, 5, 300),
(22, 4, 300),
(22, 3, 300),
(22, 2, 300),
(22, 1, 395),
(22, 0, 300)

I am trying to find the number of consecutive values that are the same.

The value field represents some data that should be change on each entry (but need not be unique overall).

The problem is to find out when there are more than two consecutive rows with the same value (given the same id).

Thus I'm looking for an output like this:

id  ind   val   count
21  5     300   1
21  4     310   1
21  3     300   2
21  2     300   2
21  1     345   1
21  0     300   1
22  5     300   4
22  4     300   4
22  3     300   4
22  2     300   4
22  1     395   1
22  0     300   1

I'm aware this is similar to the island and gaps problem discussed here.

However, those solutions all hinge on the ability to use a partition statement with values that are supposed to be consecutively increasing.

A solution that generates the ranges of "islands" as an intermediary would work as well, e.g.

id  startind   endind
21  3          2
22  5          2

Note that there can be many islands for each id.

I'm sure there is a simple adaptation of the island solution, but for the life of me I can't think of it.

Upvotes: 0

Views: 155

Answers (2)

shawnt00
shawnt00

Reputation: 17915

The other solution is obviously more elegant. I'll have to study it a little closer myself.

with agg(id, min_ind, max_ind, cnt) as (
    select id, min(ind), max(ind), count(*)
    from
        (
        select id, ind, val, sum(brk) over (partition by id order by ind desc) as grp
        from
            (
            select 
                id, ind, val,
                coalesce(sign(lag(ind) over (partition by id, val order by ind desc) - ind - 1), 1) as brk
            from myValues
            ) as d
        ) as d
    group by id, grp
)
select v.id, v.ind, v.val, a.cnt
from myValues v inner join agg a on a.id = v.id and v.ind between min_ind and max_ind
order by v.id, v.ind desc;

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

find the continuous group and then do a count() partition by that

select  id, ind, val, count(*) over (partition by id, val, grp)
from
(
    select  *, grp = dense_rank() over (partition by id, val order by ind) - ind
    from    myValues
) d
order by id, ind desc

Upvotes: 2

Related Questions