Using ROW_NUMBER() to Partition DataSets

Question

I'm a bit confused on using ROW_NUMBER() in SQL.

have a dataset that currently looks as follows:

 Number ID
    1   1
    2   6
    3   11

I have written a query below:

SELECT rownum = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Number), Number
FROM #SEQNUMBERSTEMP

Which is giving the output:

rownum  Number
1       1
1       2
1       3
1       4
1       5
1       6
1       1
1       2
1       3
1       4
1       1

But I want to achieve the following:

rownum  Number
1       1
1       2
1       3
1       4
1       5
2       1    # Note 2 should start at position 6
2       2
2       3
2       4
2       5
3       1    # Note 3 should start at position 11
3       2
3       3

Answer 1

You probably want to use DENSE_RANK() without a PARTITION BY clause, like :

SELECT rownum = DENSE_RANK() OVER (ORDER BY Number), Number
FROM #SEQNUMBERSTEMP