I want to calculate the dense rank over a group by clause and set my rank in order of my line id i.e the smallest ids get the first rank and so on.
I am able to retrieve the dense rank of my lines but the rank applied rank is not in the order I want. Sample data:
id rang prop1 prop2 prop3 prop4 ----------- -------------------- ----------- ----------- --------- ----- 1244834 2 609 96 0,02 0,00 1244835 2 609 96 0,02 0,00 1244836 2 609 96 0,02 0,00 1244837 1 609 96 0,00 0,01 1244838 1 609 96 0,00 0,01 1244839 1 609 96 0,00 0,01
my request is as follow :
select id,
DENSE_RANK() over (order by prop1, prop2, prop3, prop4) rang
prop1,
prop2,
prop3,
prop4,
from mytable
where ...
order by id
I want the calculated rank to be applied following my id column order, knowing that the ids are consecutive.
My real data has 15 more properties that are used for the ranking, they are not usefull for the ordering
In my case I want rang = 1 for id 124834, 124835, 124836 and rang = 2 for id 124837, 124838, 124839
Advertisement
Answer
If I understand correctly then you first need to find minimum id per prop1, prop2, prop3, prop4 group and calculate rank on that:
WITH cte AS (
SELECT *, MIN(id) OVER (PARTITION BY prop1, prop2, prop3, prop4) AS min_id
FROM (VALUES
(1244834, 609, 96, 0.02, 0.00),
(1244835, 609, 96, 0.02, 0.00),
(1244836, 609, 96, 0.02, 0.00),
(1244837, 609, 96, 0.00, 0.01),
(1244838, 609, 96, 0.00, 0.01),
(1244839, 609, 96, 0.00, 0.01)
) t(id, prop1, prop2, prop3, prop4)
)
SELECT *, DENSE_RANK() OVER (ORDER BY min_id) AS rnk
FROM cte
ORDER BY rnk, id