Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 down vote accepted

You can use ROW_NUMBER to generate what the rank should be

;WITH ranks AS
       GroupID, Rank, 
   GroupID, MIN(newRank), MIN(Rank)
   newRank < Rank -- don't need <> here

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

share|improve this answer
Fantastic answer, very informative! The 3 conditions you mentioned shouldn't apply, so I'm okay there. I'm fairly sure I don't want RANK or DENSE_RANK; I understand what you mean by equal values, but what do you mean exactly by "joint 2nd"? – Tedderz Dec 27 '11 at 17:11
@Tedderz - He means two values that are tied, for example, for second place. Do you want the value immediately after them to be ranked third (DENSE_RANK) or fourth (just RANK, and no value has a rank of third)? – Nick Chammas Dec 27 '11 at 17:26
Well, the PK on the table is GroupID, Rank, so within the same group there should never be ties for the value of rank. So I'm just sticking with row number. Though, I thought when he mentioned "equal values" he was referring to the case of ties and that "joint 2nd" meant something else. – Tedderz Dec 27 '11 at 18:26

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
share|improve this answer
I guess this is the fastest way. However, if rank=1 is deleted, it will never be taken. – Guillermo Gutiérrez Aug 30 '13 at 15:47
That can be remedied with some conditions, but it also fails if there's a gap of more than 1. – Snowbody Sep 12 '13 at 19:52

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.

share|improve this answer
        DECLARE @GroupIdPrevious BIGINT, @GroupRankPrevious BIGINT
        DECLARE @GroupId BIGINT, @GroupRank BIGINT

        SET @GroupId = 2

        DECLARE GroupAssignmentCursor CURSOR FAST_FORWARD FOR
               GroupId = @GroupId

        OPEN GroupAssignmentCursor

        FETCH NEXT FROM GroupAssignmentCursor INTO @GroupIdPrevious, @GroupRankPrevious

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

        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

share|improve this answer
I have the complete answer I am getting an error while posting – Ravi Ramaswamy Mar 18 '13 at 22:28

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.