title: SQL RANK() over PARTITION on joined tables
body: <p>I have two tables RSLTS and CONTACTS:</p>
  
  <p><strong>RSLTS</strong></p>
  
  <pre><code> QRY_ID  |  RES_ID  |  SCORE
  -----------------------------
     A     |    1     |    15
     A     |    2     |    32
     A     |    3     |    29
     C     |    7     |    61
     C     |    9     |    30
  </code></pre>
  
  <p><strong>CONTACTS</strong></p>
  
  <pre><code> C_ID  |  QRY_ID  |  RES_ID
  ----------------------------
    1    |    A     |    2
    2    |    A     |    1
    3    |    C     |    9
  </code></pre>
  
  <p>I'm trying to create a report that would show, for each <strong>CONTACT</strong> record (<code>C_ID</code>), the <code>RANK()</code> of <code>RES_ID</code> (by <code>SCORE</code>) in the <strong>RSLTS</strong> table within its group (<code>QRY_ID</code>). Using the data above, it would look like this:</p>
  
  <pre><code> C_ID  |  QRY_ID  |  RES_ID  |  SCORE  |  Rank
  -----------------------------------------------
    1    |    A     |    2     |    32   |   1
    2    |    A     |    1     |    15   |   3
    3    |    C     |    9     |    30   |   2
  </code></pre>
  
  <p>So far, I tried this but it returns Rank = 1 for the last row (and rank = 2 for the second which is also wrong)</p>
  
  <pre><code>SELECT
      C.*
      ,R.SCORE
      ,RANK() OVER (PARTITION BY R.QRY_ID ORDER BY R.SCORE DESC)
  FROM CONTACTS C LEFT JOIN RSLTS R
  ON C.RES_ID = R.RES_ID
  AND C.QRY_ID = R.QRY_ID
  </code></pre>
  
  <p>UPDATE: <a href="http://sqlfiddle.com/#!3/6ef2f/1" rel="nofollow noreferrer">SQLFiddle</a></p>