title: row_number with partition value changing
body: <p>I have this piece of sql code that finds the row_number based on some values in a table __working that joins into a lookup table __Eval</p>
  
  <pre><code>;WITH r 
   AS (
      select 
  
      w.uid,
      t.TypeId,
      --weight
      ROW_NUMBER () OVER (PARTITION BY w.uid ORDER BY  DIFFERENCE(t.val1, w.val1) +  DIFFERENCE(t.val2, w.val2) + DIFFERENCE(t.val3, w.val3) + DIFFERENCE(t.val4, w.val4)  DESC) as Score
      ,w.account
  
      from __Working w
          join __eval t on w.val1 like t.val1 and IsNull(w.val4, '') like t.val4 and IsNull(w.val2, '') like t.val2 and IsNull(w.val3, '') like t.val3
  
  )
  
  select * from r     where   r.account = 1 and score = 1
  </code></pre>
  
  <p>this returns a typeId = 1</p>
  
  <p>however if I write it like this</p>
  
  <pre><code>   ;WITH r 
       AS (
          select 
  
          w.uid,
          t.TypeId,
          --weight
          ROW_NUMBER () OVER (PARTITION BY w.uid ORDER BY  DIFFERENCE(t.val1, w.val1) +  DIFFERENCE(t.val2, w.val2) + DIFFERENCE(t.val3, w.val3) + DIFFERENCE(t.val4, w.val4)  DESC) as Score
          ,w.account
  
          from __Working w
              join __eval t on w.val1 like t.val1 and IsNull(w.val4, '') like t.val4 and IsNull(w.val2, '') like t.val2 and IsNull(w.val3, '') like t.val3
              where   r.account = 1
      )
  
      select * from r     where   r.account = 1 and score = 1
  </code></pre>
  
  <p>it returns TypeId = 2.  I would expect that if i had multiple UIDs across different accounts in __working, but I don't.  What am i missing here?</p>