asked on

Reorder ranking

Hello I have the following table and data below. How can I reorder the rank column if say the product with the 5 rank is deleted. An update statement to reorder the rank once an element has been deleted? How would you reorder the rank when the rank must stay 1 through 8 after the delete?

Id pId      type       rank
68      293552      DVD      1
66      297906      DVD      2
65      298007      DVD      3
67      283870      DVD      4
69      285618      DVD      5
70      293129      DVD      6
71      288795      DVD      7
74      288723      DVD      8
73      289583      DVD      9

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gogetsome

ASKER

Exactly! Thanks for your help!

This is what I came up with that works perfectly:

      @TopSellersId int

      AS

      Declare @Rank int
      Set @Rank = (Select [Rank] From TopSellers Where TopSellersId = @TopSellersId)
      Declare @Type Varchar(20)
      Set @Type = (Select [Type] From TopSellers Where TopSellersId = @TopSellersId)

Delete TopSellers Where TopSellersId = @TopSellersId

Update TopSellers Set [Rank] = ([Rank] - 1) Where [rank] > @Rank and [Type] = @Type