Below is the table:
x
category weightage As_of_date 123abc 50 1/1/2020456abc 100 1/2/2020456abc 100 1/3/2020678def 200 1/4/2020678def 200 1/4/2020123def 50 2/1/2020123def 50 2/1/2020123def 50 2/3/2020123def 50 2/1/2020123def 50 6/7/2020where I want to rank the category based on weightage desc, expected results:
category weightage As_of_date dense_rank 123abc 50 1/1/2020 4456abc 100 1/2/2020 3456abc 100 1/3/2020 3678def 200 1/4/2020 1678def 200 1/4/2020 1123def 50 2/1/2020 2123def 50 2/1/2020 2123def 50 2/3/2020 2 123def 50 2/1/2020 2123def 50 6/7/2020 2 what was already tried: select desnse_rank() over (partition by category order by weightage desc), but I need to rank it based on sum(weightage) per category.
Advertisement
Answer
With a CTE you can do the calculations one by one. First calculate the dense rank for sum per category (ranked_by_sum), then join back to the original table to get the dense rank value for the individual rows:
WITH test_data (category, weightage, as_of_date) AS(SELECT '123abc',50, TO_DATE('1/1/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '456abc',100,TO_DATE('1/2/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '456abc',100,TO_DATE('1/3/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '678def',200,TO_DATE('1/4/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '678def',200,TO_DATE('1/4/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '123def',50, TO_DATE('2/1/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '123def',50, TO_DATE('2/1/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '123def',50, TO_DATE('2/3/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '123def',50, TO_DATE('2/1/2020','DD/MM/YYYY') FROM DUAL UNION ALLSELECT '123def',50, TO_DATE('6/7/2020','DD/MM/YYYY') FROM DUAL), ranked_by_sum (category,sum_weightage, drnk)AS(SELECT category, SUM(weightage),DENSE_RANK () OVER ( ORDER BY SUM(weightage) DESC ) FROM test_data GROUP BY category)SELECT t.category, t.weightage, t.as_of_date, r.drnk FROM test_data t JOIN ranked_by_sum r ON t.category = r.category ORDER BY r.drnk DESCCATEGO WEIGHTAGE AS_OF_DATE DRNK------ ---------- ----------- ----------123abc 50 01-JAN-2020 4456abc 100 01-FEB-2020 3456abc 100 01-MAR-2020 3123def 50 02-JAN-2020 2123def 50 06-JUL-2020 2123def 50 02-JAN-2020 2123def 50 02-JAN-2020 2123def 50 02-MAR-2020 2678def 200 01-APR-2020 1678def 200 01-APR-2020 1