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

Question

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?

Answer 1

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 < Rank -- don't need <> 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

Answer 2

Could a self-join do the trick?

select MIN(a.rank)+1 from dbo.GroupAssignment a
left join dbo.GroupAssignment b on a.GroupID = b.GroupID AND a.rank+1 = b.rank
where a.GroupID = ? and b.rank is null

Answer 3

You could also add a DELETE trigger to the Group Assignment table that stores any removed ranks in a separate table. The procedure that inserts new entries would then check the reuse table (selecting the lowest available for the group) before performing the insert.

Answer 4

        DECLARE @GroupIdPrevious BIGINT, @GroupRankPrevious BIGINT
        DECLARE @GroupId BIGINT, @GroupRank BIGINT

        SET @GroupId = 2

        DECLARE GroupAssignmentCursor CURSOR FAST_FORWARD FOR
        SELECT
                GroupID
              ,GroupRank
          FROM
               #GroupAssignment
         WHERE
               GroupId = @GroupId

        OPEN GroupAssignmentCursor

        FETCH NEXT FROM GroupAssignmentCursor INTO @GroupIdPrevious, @GroupRankPrevious

        IF @@FETCH_STATUS = 0
        BEGIN
            FETCH NEXT FROM GroupAssignmentCursor INTO @GroupId, @GroupRank
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @GroupRank - @GroupRankPrevious = 1 -- There is no gap
                BEGIN
                    SET @GroupIdPrevious = @GroupId
                    SET @GroupRankPrevious = @GroupRank
                    FETCH NEXT FROM GroupAssignmentCursor INTO @GroupId, @GroupRank
                END
                ELSE
                BEGIN
                    SELECT @GroupIdPrevious AS GroupIdPrevious, @GroupRankPrevious AS GroupRankPrevious, @GroupId AS GroupId, @GroupRank AS GroupRank, 'Found gap' AS OutputMsg
                    BREAK
                END
            END
        END

        CLOSE GroupAssignmentCursor
        DEALLOCATE GroupAssignmentCursor

====================================================================== Another way is to a loop with Cursor. here are the steps (I am just typing this - you need to make sure the syntax is correct)

1. Declare the cursor
2. Fetch the 1st row
3. Save the Id in a local variable (Previous Id)
4. Fetch the 2nd row
5. Start a WHILE Loop WHEN NOT EOF (@@FETCH_STATUS....)
6. Compare the Current Id with the Previous Id
7. If the difference is 1 skip
8. if not set the value or print
9. Fetch Next

If you are interested, I can write the complete code and post it

Ravi Ramaswamy