SQL Server Ranking issue

Question

I am trying to apply ranking to my data set the logic is as follows:

For each ID , Order by ID2 ASC and Order by IsMaster Desc rank the row 1 and only change it when the ID4 value changes

My dataset and desired output looks like:

Test data

CREATE TABLE Test_Table 
(ID INT ,ID2 INT, IsMaster INT, ID4 VARCHAR(10))
GO

INSERT INTO Test_Table  (ID ,ID2 , IsMaster , ID4 )
VALUES 
 (1,    101,    1   ,'AAA')  -- 1  <-- Desired output for rank
,(1,    102,    0   ,'AAA')  -- 1
,(1,    103,    0   ,'AAB')  -- 2
,(1,    104,    0   ,'AAB')  -- 2
,(1,    105,    0   ,'CCC')  -- 3
,(2,    101,    1   ,'AAA')  -- 1
,(2,    102,    0   ,'AAA')  -- 1
,(2,    103,    0   ,'AAA')  -- 1
,(2,    104,    0   ,'AAB')  -- 2
,(2,    105,    0   ,'CCC')  -- 3

this is what I have tried so far:

SELECT *
    ,DENSE_RANK() OVER (PARTITION BY ID  ORDER BY ID2 ASC, IsMaster DESC ) rn
FROM Test_Table

please please please help me thank you.

Answer 1

Another way probably less efficient but it will work.

WITH X AS
(
 SELECT *
       ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID2) RowNum
 FROM dbo.Test_Table
)
, CTE_VehicleNumber
as
(
    SELECT  T.ID , T.ID2, t.IsMaster ,T.ID4 , t.RowNum  , 1 as [Rank]
    FROM X  as T
    WHERE T.IsMaster = 1 

    UNION ALL

    SELECT  T.ID, T.ID2, t.IsMaster ,T.ID4 , t.RowNum , CASE WHEN t.ID4 <> c.ID4 THEN 1+ C.[Rank]
                                                              ELSE 0+ C.[Rank]
                                                         END  as [Rank]
    FROM CTE_VehicleNumber as C
        inner join X  as T ON T.RowNum = C.RowNum + 1
                                              AND t.ID = c.ID
)
SELECT ID , ID2, IsMaster ,ID4  , [Rank] 
FROM CTE_VehicleNumber 
ORDER BY ID , ID2, IsMaster ,ID4  , [Rank]
OPTION (MAXRECURSION 0);