SQL Adding row numbers

2
votes

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

2
sqlsql-server

2 Answers

6
votes

Use DENSE_RANK():

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

enter image description here

Demo

0
votes

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;