Find the first gap in an aggregation of integers 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