SQL: Ranking Sections separately of a Rollup over multiple columns

Question

I try to do a Rollup over multiple columns and then apply a ranking on each stage/section of the rollup process. The result should look somewhat like the following:

| ColA | ColB | ColC | RankingCriteria | Ranking |
|------|------|------|-----------------|---------|
| -    | -    | -    | 10              | 1       |
|------|------|------|-----------------|---------|
| A    | -    | -    | 10              | 1       |
| B    | -    | -    | 8               | 2       |
|------|------|------|-----------------|---------|
| A    | a    | -    | 9               | 1       |
| A    | b    | -    | 7               | 2       |
| A    | c    | -    | 5               | 3       |
| A    | d    | -    | 2               | 4       |
|------|------|------|-----------------|---------|
| B    | a    | -    | 8               | 1       |
| B    | c    | -    | 7               | 2       |
| B    | b    | -    | 2               | 3       |
|------|------|------|-----------------|---------|
| A    | a    | x    | 7               | 1       |
| A    | a    | y    | 5               | 2       |
| A    | a    | z    | 4               | 3       |
|------|------|------|-----------------|---------|
| A    | b    | y    | 6               | 1       |
|------|------|------|-----------------|---------|
| A    | c    | w    | 10              | 1       |
| A    | c    | y    | 10              | 1       |
| A    | c    | z    | 8               | 2       |
| A    | c    | x    | 6               | 3       |
|------|------|------|-----------------|---------|
| A    | d    | y    | 4               | 1       |
|------|------|------|-----------------|---------|
| B    | a    | w    | 10              | 1       |
| B    | a    | x    | 8               | 2       |
|------|------|------|-----------------|---------|
| B    | b    | y    | 6               | 1       |
| B    | b    | z    | 5               | 2       |
| B    | b    | w    | 4               | 3       |
|------|------|------|-----------------|---------|
| B    | c    | x    | 6               | 1       |
|------|------|------|-----------------|---------|

So as you can see each grouping set has it's own ranking.

The basic Rollup-Query for this is simple but the ranking is giving me headaches and I am running out of ideas on how to achieve this.

Select ColA, ColB, ColC, RankingCriteria
From table
Group By Rollup(ColA, ColB, ColC)

The problem is that I cannot use a normal Rank() over (Partition by ...) because there is no partition I could use that'd work on the whole thing.

Answer 1

I think this will produce what you want:

SELECT r.*,
       row_number() over (partition by (case when colb is null and colc is null and cola is not null
                                             then 1 else 0 end),
                                       (case when colb is null and colc is null and cola is not null
                                             then NULL else A end),
                                       (case when colb is null and colc is null and cola is not null
                                             then NULL else B end)
                         order by RankingCriteria desc) as seqnum                           
FROM (Select ColA, ColB, ColC, RankingCriteria
      From table
      Group By Rollup(ColA, ColB, ColC)
     ) r;

The way I read the logic is that partitioning by A and B works for all but the second group. That is why this uses the three case statements.