如何通过SQL实现按ID分组生成递增组标识列？

Problem Overview #

We have a table table1 with duplicate Id values, and we need to add an identifier column that assigns the same incrementing number to all rows in each Id group.

Original Table Data #

Id          Val
1111111111  Andre
1111111111  Bart
1111111111  Corry
2222222222  Donald
2222222222  Eric
3333333333  Fiona

Desired Output #

GroupNumber  Id          Val
1            1111111111  Andre
1            1111111111  Bart
1            1111111111  Corry
2            2222222222  Donald
2            2222222222  Eric
3            3333333333  Fiona

Your Current Solution #

Your approach using nested ROW_NUMBER() functions is clever and works correctly:

SELECT 
  ( row_number() OVER (ORDER BY id) - row_number() OVER (PARTITION BY id ORDER BY id) + 1 ) nGroupedToRow,
  row_number() OVER (ORDER BY id) nRow,
  row_number() OVER (PARTITION BY id ORDER BY id) nNumDupl,
  id, 
  val 
FROM table1

It effectively calculates the group number by offsetting the overall row count with the partitioned row count. That's a neat workaround!

More Optimal Alternatives #

Since your core goal is to assign a unique, incrementing number to each distinct Id group, we can use window functions built explicitly for this scenario—they're simpler, more readable, and often more efficient.

1. Use DENSE_RANK() (Best Choice) #

DENSE_RANK() assigns a unique rank to each distinct Id in the order they appear, with no gaps between ranks. This is exactly what you need:

SELECT
  DENSE_RANK() OVER (ORDER BY id) AS GroupNumber,
  id,
  val
FROM table1

This query returns exactly your desired output, with minimal code. It's immediately clear to any SQL developer what this is doing, which makes maintenance easier.

2. Use RANK() (Similar, With Caveats) #

If you ever encountered a scenario where you wanted gaps in group numbers (though not needed here), RANK() would work. In your case, since all rows for the same Id are consecutive, it will return the same result as DENSE_RANK(). However, if there were non-consecutive rows for the same Id, RANK() would skip numbers (e.g., if an Id had rows at positions 1,3,4, it would get rank 1, and the next Id would get rank 4), while DENSE_RANK() would still assign rank 2 to the next Id.

Why These Are Better #

• Simplicity: No need for arithmetic on multiple ROW_NUMBER() results—just one straightforward window function.
• Readability: The intent of DENSE_RANK() is obvious to anyone familiar with window functions, unlike the row number subtraction trick.
• Performance: Most SQL engines optimize DENSE_RANK() better than nested ROW_NUMBER() calculations, especially on large datasets.

Your original solution is totally valid, but these alternatives are the idiomatic way to solve this problem in SQL!

内容的提问来源于stack exchange，提问作者Andre Nel