Calculate the rank and apply it following an order

Question

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

Answer 1

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