Create number sequence based on two columns

Question

I'm trying to generate number sequence based on 2 columns, Sno and UnitCost. The numbers should run down sequentially but they shouldn't change when both the columns are same. But if any one column is different it should increment.

I tried something with row_number(), rank(), dense_rank() but have been unable to hit the right logic.

Here's the required column and existing columns:

Sno    UnitCost   RequiredColumn
ch01   10         01
ch01   10         01
ch02   20         02
ch02   20         02
ch02   30         03
ch02   30         03
ch03   10         04

Any tips? Thanks.

Answer 1

Using DENSE_RANK:

SELECT Sno, UnitCost, DENSE_RANK() OVER (ORDER BY Sno, UnitCost) RequiredColumn
FROM yourTable;