title: Ranking results: manually breaking the tie
body: <p>I used this page: <a href="http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx" rel="nofollow noreferrer">Ranking Criteria</a></p>
  
  <p>To write this:</p>
  
  <pre><code>with cte as (
  Select t.[Client ID]
        ,t.[Location]
        ,Count(t.[Location]) [Loc Count]
  FROM #tmp_PCL_coreData t
  Group By  t.[Client ID]
          , t.[Location])
  
  Select *
        ,(SELECT COUNT(*) FROM cte b
          WHERE a.[Client ID] = b.[Client ID] AND a.[Loc Count] &lt;= b.[Loc Count]) [Rank]
  FROM cte a
  </code></pre>
  
  <p>And it works fine for giving me ranked locations for each <code>[Client ID]</code>.  The only downside is that when the <code>[Loc Count]</code> is the same for each <code>[Location]</code> then the rank is the same for each.  In my particular case I need to arbitrarily assign one or the other with the "better" rank.  Any ideas on how I can achieve this?</p>
  
  <p>Example:</p>
  
  <p><img src="https://i.stack.imgur.com/ldgxk.png" alt="enter image description here"></p>
  
  <p>You can see that Client 60756 has 1 each in Redmond and Bellevue, giving both values a Rank 2.  Either one can be Rank 1 for my purposes, but I definitely need a rank 1.  For now, it makes sense for me to assign rank 1 alphabetically so that if I come up with a better tie-breaking criteria later I can see where to build that logic in.</p>