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 &lt; Rank -- don't need &lt;&gt; 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