Question 192642 in Mysql Question posted in
The official MySQL documentation can be found here.

Using MySQL, How to Set Unique Sequence Number WITHIN a Group

How can I create an UPDATE statement using MySQL to set a unique sequential value WITHIN clusters / groups of records?

I have a table with columns for UniqueID, State ("AL", "CA", etc.), PersonName, and StateID. I need to number the StateID for each person starting at 1 for each State.

Here is my setup:

CREATE TABLE MyData( UniqueID VARCHAR(64), State VARCHAR(64), PersonName VARCHAR(64), StateID INT );
INSERT INTO MyData VALUES ( UUID(), 'CA', 'David', NULL ), ( UUID(), 'CA', 'Ann', NULL ), ( UUID(), 'CA', 'Bob', NULL ),
                          ( UUID(), 'AL', 'Mary', NULL ), ( UUID(), 'AL', 'Tom', NULL ), ( UUID(), 'AL', 'Evelyn', NULL );

I would like StateID to start at 1 and increment for each record WITHIN EACH STATE, but then RESET to 1 for EACH NEW STATE. The sequencing does NOT have to be alphabetical.

Desired outcome:

someIDvalue AL Mary   1
someIDvalue AL Tom    2
someIDvalue AL Evelyn 3
someIDvalue CA David  1
someIDvalue CA Ann    2
someIDvalue CA Bob    3

So far I am able to do this:

    UPDATE MyData
INNER JOIN (    SELECT UniqueID, (SELECT @RowNumber := @RowNumber +1) NewStateID
                  FROM MyData
            INNER JOIN ( SELECT @RowNumber := 0 ) InternalVariable
              ORDER BY MyData.State ) ReorderedSet ON MyData.UniqueID = ReorderedSet.UniqueID
       SET MyData.StateID = ReorderedSet.NewStateID;

But this doesn’t reset the sequencing when the state changes.

I don’t think using GROUP works because I don’t need aggregation – only to know when the state changes from the previous record.

Any help on this would be very appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you very much to @Adesegun Daniel; here is a slight modification which ended up being a definitive solution.

    SET @row_number:=0; 
SET @state:=(SELECT State FROM MyData ORDER BY State LIMIT 1); 
UPDATE MyData SET MyData.StateID = (@row_number:= CASE WHEN @state=State THEN @row_number+1 ELSE 1 END), State = (@state := State) 
ORDER BY State, UniqueID;

  2. 0

    You can use the following MySQL query to set a unique sequential value within clusters/groups of records

    SET @row_number:=0; SET @state:=''; UPDATE MyData SET StateID = (@row_number:=CASE WHEN @state=State THEN @row_number+1 ELSE 1 END) AND @state:=State ORDER BY State, PersonName;

    This query will reset the sequencing when the state changes. The @row_number variable is used to keep track of the sequence number and the @state variable is used to keep track of the current state. The ORDER BY clause is used to order the records by state and person name.

    I hope this helps!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search