How to reset DENSE_RANK

Question

I have the following table

Id,Cat1,Cat2,ColA,ColB,

1,1,1,,

2,1,1,,

3,1,2,,

4,1,3,,

5,2,11,,

6,2,12,,

7,2,12,,

I need to add a unique sequential number per grouping column of Cat2 starting at 1 BUT it has to reset back to 1 when Cat1 changes

My output should be

Row, Id,Cat1,Cat2,ColA,ColB,

1,1,1,1,,

1,2,1,1,,

2,3,1,2,,

3,4,1,3,,

1,5,2,11,,

2,6,2,12,,

2,7,2,12,,

DENSE_RANK() works to give a unique value e.g.

Row= DENSE_RANK() OVER ( ORDER BY Cat2)

but I need it to reset itself based of another column Cat1

Answer 1

Use the PARTITION BY clause on Cat1:

Row = DENSE_RANK() OVER ( PARTITION BY Cat1 ORDER BY Cat2)

The PARTITION BY clause will partition your DENSE_RANK() first based on your Cat1 before ranking them densely, which is exactly what you need.