How can I add an incremental number to each element in a grouped series?

Question

I am facing a problem where I have a table called Group and a table called Entry. The Group table has a primary key Id. The Entry table has a primary key Id, a foreign key to the Group table's Id called GroupId. The Entry table has one more column Weight. This Weight is an integer and all it does is tell me the sort weight where zero is shown at the top.

Basically what happened is, someone made this Weight field nullable when the database was designed. Now I need to go through and adjust the Weight to fit the UNIQUE constraint we intend on adding: UNQIUE(GroupId, Weight) to the Entry table. This prevents two Entry entries from having the same sort weight when they're in the same group basically.

What query would let me go through all of our existing data and simply number the Weight column on each of the entries on a group by group basis from 0 to N where N is the number of Entry entries in a Group? I want to set the weight based on the Id of the Entry so that the lowest Id for an Entry in a given Group gets the lowest Weight.

I want one big ol' query that will go through the Group table, join up all of the Entry entries and then iterate through them and assign the sequence. But, I have no clue where to start on that.

Answer 1

Essentially you would use the MS SQL Over Clause

Here is an sqlfiddle sample to show you how to do it in one query

Hope this helps.

CREATE TABLE Entry (Id INT PRIMARY KEY, GroupId INT, Weight INT);

CREATE TABLE [Group] (Id INT PRIMARY KEY, Label VARCHAR(10));

INSERT [Group] (Id, Label)
VALUES(1, 'test');


INSERT Entry (Id, GroupId, Weight)
VALUES(1,1, null)

INSERT Entry (Id, GroupId, Weight)
VALUES(2,1, null)

INSERT Entry (Id, GroupId, Weight)
VALUES(3,1, null)

UPDATE E
  SET E.WEIGHT = o.WEIGHTIS
--SELECT *
FROM Entry E
INNER JOIN (
    SELECT G.Id GID, E.Id EID, 
     ROW_NUMBER() OVER(PARTITION BY G.Id ORDER BY E.ID) WeightIs
    FROM [Group] G
    INNER JOIN Entry E
      ON G.Id = E.GroupId  
) o
ON o.GID = E.GroupID AND o.EID = E.Id