greener greener - 3 months ago 7
SQL Question

SQL RANK() over PARTITION on joined tables

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

Thanks in advance for your help!

Answer

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