CTE ROW_NUMBER() OVER PARTITION based on a value

Question

SQL Server T-SQL CTE ROW_NUMBER() OVER PARTITION based on a value

Want numbers based on NAME, VAL1, and VAL2 columns, then order by DT dates descending.

WITH cteA (NAME, VAL1, VAL2, DT) AS 
(
    SELECT 'A', '7100', 'PN1', '2023-03-01' UNION
    SELECT 'A', '7100', 'PN1', '2023-01-01' UNION
    SELECT 'A', '7100', 'PN3', '2022-09-01' UNION
    SELECT 'A', '7100', 'PN1', '2022-05-20' UNION
    SELECT 'A', '7100', 'PN1', '2022-05-09' UNION
    SELECT 'A', '7100', 'PN1', '2022-08-20'
), 
cteB AS 
(
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY NAME, VAL1, VAL2 
                           ORDER BY DT DESC) ROWNUMBER
    FROM cteA
)
SELECT *
FROM cteB
ORDER BY DT DESC

Using the ROW_NUMBER OVER PARTITION BY NAME, VAL1, and VAL2 but the numbers on the results are not as desired:

NAME	VAL1	VAL2	DT	ROWNUMBER
A	7100	PN1	2023-03-01	1
A	7100	PN1	2023-01-01	2
A	7100	PN3	2022-09-01	1
A	7100	PN1	2022-08-20	3
A	7100	PN1	2022-05-20	4
A	7100	PN1	2022-05-09	5

The numbers need to be separated by VAL2 column: PN1 (first 2 rows before PN3), PN3 itself, & PN1 (last 3 rows after PN3),

and keep DT descending,

I am expecting numbers (ROWNUMBER column) on the result like this:

NAME	VAL1	VAL2	DT	ROWNUMBER
A	7100	PN1	2023-03-01	1
A	7100	PN1	2023-01-01	1
A	7100	PN3	2022-09-01	2
A	7100	PN1	2022-08-20	3
A	7100	PN1	2022-05-20	3
A	7100	PN1	2022-05-09	3

Answer 1

This is a variation of a gaps-and-island problem, you need an intermediary step to identify the islands (Val2) which you can do using lag to mark when the value changes, a running total then gets your row number:

with cteA (NAME, VAL1, VAL2, DT) as 
(
  select 'A', '7100', 'PN1', '2023-03-01' union
  select 'A', '7100', 'PN1', '2023-01-01' union
  select 'A', '7100', 'PN3', '2022-09-01' union
  select 'A', '7100', 'PN1', '2022-05-20' union
  select 'A', '7100', 'PN1', '2022-05-09' union
  select 'A', '7100', 'PN1', '2022-08-20'
), 
cteB as 
(
select *,
  case when Lag(val2,1,1) over (partition by NAME, VAL1 order by DT desc) 
   != val2 then 1 
  end gp
from cteA
)
select *, 
  Sum(gp) over(partition by NAME, VAL1 order by DT desc) Rownumber
from cteB
order by DT desc;