Search code examples
sql-servert-sqlrow-number

Get the unique row number for the same flag when it changes to other flag in the next row?

I am new to T-SQL.

Can anyone please help me to get this?

I have the following data:

Id  flg  rnum
--------------
11  n   1
11  n   2
11  y   3
11  n   4
11  n   5
11  y   6
11  n   7

I want to get the unique row number for the same flag when it changes to other flag in the next row.

Desired result:

Id  flg  rnum ranks
-------------------
11  n   1    1
11  n   2    1
11  y   3    2
11  n   4    3
11  n   5    3
11  y   6    4  
11  n   7    5

Thank you for your help.

Solution

  • You can use LAG() OVER ([ PARTITION BY .. ] ORDER BY...) window analytic function together with another analytic function SUM() OVER ([ PARTITION BY .. ] ORDER BY...) :

    WITH T2 AS
(
SELECT *, LAG(flg,1) OVER ( ORDER BY rnum ) as lg
 FROM T  --> your original table  
)
SELECT id, flg, rnum,
       SUM(CASE WHEN flg=lg THEN 0 ELSE 1 END) OVER ( ORDER BY rnum ) AS ranks 
  FROM T2

    Demo