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?
Advertisement
Answer
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;