Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

4 Answers 4

up vote 7 down vote accepted

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

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
2  
@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
add comment

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
add comment

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
add comment
        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

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

Your Answer

 
discard

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.