SQL Adding row numbers

Question

I am looking for a way to add row numbers, but adding duplicated row numbers when one of the columns are duplicates

Logic * Every time Col1 always start RowNo from 1 * Every time Col1 + Col2 are the same use the same RowNo

Table1

Col1    Col2
1       A       
1       B       
1       B       
2       C       
2       D       
2       E       
3       F       
4       G       

Output should be

Col1    Col2  RowNo
1       A       1
1       B       2
1       B       2
2       C       1
2       D       2
2       E       3
3       F       1
4       G       1

I have tried,but the output is not correct select col1,col2 ,row_number() over(partition by (col1+col2) order by col1) from Table1

Answer 1

You can use row_number window function with partitioning on the col1 column and ordering on col2

select t.*,
    row_number() over (partition by col1 order by col2) as col3
from your_table t;

Answer 2

Use DENSE_RANK():

SELECT Col1, Col2,
    DENSE_RANK() OVER (PARTITION BY Col1 ORDER BY Col2) RowNo
FROM yourTable;
ORDER BY Col1, Col2;

Demo