Menu
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
523
The RANK() function is a window function that assigns a rank to each row in the partition of a result set. The rank of a row is determined by one plus the number of ranks that come before it. RANK() OVER ( PARTITION BY <expr1>[{,<expr2>...}] ORDER BY <expr1> [ASC|DESC], [{,<expr2>...}] )
To partition rows and rank them by their position within the partition, use the RANK() function with the PARTITION BY clause. SQL's RANK() function allows us to add a record's position within the result set or within each partition. In our example, we rank rows within a partition.
we can use rank function and group by in the same query set but all the columns should be contained in either aggregate function or the Group by clause.
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
