SQL RANK() over PARTITION on joined tables

Question

I have two tables RSLTS and CONTACTS:

RSLTS

 QRY_ID  |  RES_ID  |  SCORE
-----------------------------
   A     |    1     |    15
   A     |    2     |    32
   A     |    3     |    29
   C     |    7     |    61
   C     |    9     |    30

CONTACTS

 C_ID  |  QRY_ID  |  RES_ID
----------------------------
  1    |    A     |    2
  2    |    A     |    1
  3    |    C     |    9

I'm trying to create a report that would show, for each CONTACT record (C_ID), the RANK() of RES_ID (by SCORE) in the RSLTS table within its group (QRY_ID). Using the data above, it would look like this:

 C_ID  |  QRY_ID  |  RES_ID  |  SCORE  |  Rank
-----------------------------------------------
  1    |    A     |    2     |    32   |   1
  2    |    A     |    1     |    15   |   3
  3    |    C     |    9     |    30   |   2

So far, I tried this but it returns Rank = 1 for the last row (and rank = 2 for the second which is also wrong)

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

UPDATE: SQLFiddle

Solution

As the rank doesn't depend at all from the contacts

RANKED_RSLTS

 QRY_ID  |  RES_ID  |  SCORE |  RANK
-------------------------------------
   A     |    1     |    15  |   3
   A     |    2     |    32  |   1
   A     |    3     |    29  |   2
   C     |    7     |    61  |   1
   C     |    9     |    30  |   2

Thus :

SELECT
    C.*
    ,R.SCORE
    ,MYRANK
FROM CONTACTS C LEFT JOIN
(SELECT  *,
 MYRANK = RANK() OVER (PARTITION BY QRY_ID ORDER BY SCORE DESC)
  FROM RSLTS)  R
ON C.RES_ID = R.RES_ID
AND C.QRY_ID = R.QRY_ID

Comments

[Clockwork-Muse]

Are you sure? I get your expected results. Also, what's up with all the abbreviations? And might you really mean DENSE_RANK() (which will close 'gaps' between rankings)?

[greener]

Thanks for the SQLFiddle. The data I have here locally showed something different. I updated the data in this SQLFiddle (sqlfiddle.com/#!3/6ef2f/1) where the last record should show rank = 2 instead of 1 since 61 > 30

[Clockwork-Muse]

At the moment your results are equivalent to RANK() OVER(ORDER BY r.score DESC).... Are you sure you need to partition (you don't have any repetitions in your ranking).

[jotasi]

Please do not provide a code-only answers, but explain why/how the solution is working. Also please edit your code to show correctly.

[sai wang]

Sure,will do next time.Just a newbie and try to answer the question.Thanks for your advise.

[jotasi]

You can still edit your post and add an explanation!