Get difference from top ranked item

user_2096512 0
Viewed 60

I'm using the following query to rank items, but I need it to also show the difference between the item and the top ranked item (The top ranked item will show the difference between it and the 2nd ranked item.), like so:

SELECT rdate, rtime, SF, DENSE_RANK() OVER (PARTITION BY rdate, rtime ORDER BY rdate, 
rtime, SF DESC) rank
FROM DailySF

rdate      |  rtime  |  SF  | rank  | DiffTop
-------------------------------------------
18/02/2021    09:00     54     1        2
18/02/2021    09:00     52     2       -2
18/02/2021    09:00     50     3       -4
19/02/2021    09:00     53     1       10
19/02/2021    09:00     43     2      -10
19/02/2021    09:00     40     3      -13
19/02/2021    09:00     35     4      -18

How do I create the DiffTop column?

sql-server sql
1 Answers

You can use window functions for this as well:

SELECT rdate, rtime, SF,
       DENSE_RANK() OVER (PARTITION BY rdate, rtime ORDER BY rdate, 
rtime, SF DESC) as rank,
       (SF - MAX(SF) OVER (PARTITION BY rdate, rtime)) as diff
FROM DailySF;

The top ranked value is the one with maximum SF.

To handle the top ranked item:

SELECT rdate, rtime, SF,
       DENSE_RANK() OVER (PARTITION BY rdate, rtime ORDER BY rdate, 
rtime, SF DESC) as rank,
       (CASE WHEN SF = MAX(SF) OVER (PARTITION BY rdate, rtime)
             THEN SF - LEAD(SF) OVER (PARTITION BY rdate, rtime ORDER BY rtime)
             ELSE SF - MAX(SF) OVER (PARTITION BY rdate, rtime)
        END) as diff
FROM DailySF;
user_11440350
Related