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

Tim Biegeleisen · Accepted Answer · 2018-01-10 11:06:43Z

6

Use DENSE_RANK():

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

Demo

answered Jan 10, 2018 at 11:06

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

@Mags Can you mark this answer correct if it solved your problem?

Gurwinder Singh · Accepted Answer · 2018-01-10 11:09:57Z

0

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;

answered Jan 10, 2018 at 11:09

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Collectives™ on Stack Overflow

SQL Adding row numbers

2 Answers 2

Demo

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related