Rank When Value is Changed in Not Ordered Dataset

Sometimes you should order your data in SQL as group of values like this:

Step 1:

get row number and lag value of data in each row

with t as (
    select *
    from (values (100),(100),(100),(10),(100),(100),(10),(10)) 
      as v(c)
),
lc as (
    select row_number() over() as rn, c, lag(c) over() as l
    from t
)
-- RESULT
-- rn   c   lag_c
-- 1   100  Null
-- 2   100  100
-- ...
-- 7   10   100
-- 8   10   10

Step 2:

Create a flag based on equality of data column and lag of data column

select rn, c, l, 
       case when l = c then null else 1 end as flag
from lc-- RESULT
-- rn   c   lag_c  flag
-- 1   100  null   null 
-- 2   100  100    null
-- ...
-- 7   10   100     1
-- 8   10   10     null

Step 3:

Join each row with Less-Than or Equal row number of itself to find all detected changes flags which exist in previous values and sum them with group by row_number & value.

select main.c as data, sum(previous.flag) as rank
from co as main join co as previous 
on main.rn >= previous.rn 
group by main.c, main.rn
order by main.rn

with t as (
    select *
    from (values (100),(100),(100),(10),(100),(100),(10),(10)) 
      as v(c)
),
lc as (
    select row_number() over() as rn, c, lag(c) over() as l
    from t
),
co as (
    select rn, c, l, case when l = c then null else 1 end as flag
    from lc
)
select main.c as data, sum(previous.flag) as rank
from co as main join co as previous on main.rn >= previous.rn 
group by main.c, main.rn
order by main.rn