title: SQL Server Ranking issue
body: <p>I am trying to apply ranking to my data set the logic is as follows:</p>
  
  <p>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 </p>
  
  <p>My dataset and desired output looks like:</p>
  
  <p><a href="https://i.stack.imgur.com/exZIU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/exZIU.png" alt="desired output]"></a></p>
  
  <p>Test data </p>
  
  <pre><code>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  &lt;-- 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
  </code></pre>
  
  <p>this is what I have tried so far:</p>
  
  <pre><code>SELECT *
      ,DENSE_RANK() OVER (PARTITION BY ID  ORDER BY ID2 ASC, IsMaster DESC ) rn
  FROM Test_Table
  </code></pre>
  
  <p>please please please help me thank you.</p>