Find the first gap in an aggregation of integers in SQL Server

Let's say I have a table called dbo.GroupAssignment.

GroupID | Rank
------------------
1 1
1 2
1 3
2 1
2 3
2 4
3 2
3 4
3 5

The PK is GroupID, Rank. Normally, the ranks within a group are a contiguous sequence of integers starting from 1. But it's possible for a GroupAssignment to get removed, leaving a gap. When a new assignment is made for the group, I want to fill the first gap available.

So, how could I calculate this in SQL Server?

You can use ROW_NUMBER to generate what the rank should be

;WITH ranks AS
(
    SELECT
       GroupID, Rank, 
       ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Rank) AS newRank
    FROM 
       dbo.GroupAssignment
)
SELECT
   GroupID, MIN(newRank), MIN(Rank)
FROM
   ranks
WHERE
   newRank  here
GROUP BY
   GroupID;

Personally, I wouldn't re-use this Rank if one of these applies

• It can be generated at runtime with ROW_NUMBER etc

• It is some kind of ID column: the old value will live in history tables etc and will be ambiguous

• It is simply an arbitrary internal key

Also, do you want RANK or DENSE_RANK to deal with "joint 2nd" or "equal values" instead of ROW_NUMBER

StackExchange Database Administrators Q#9762, answer score: 7