CODEWITHSUNDEEP
sqlsql-serverselectrow-number
Rich Jenks
Rich Jenks

Reputation: 1773

Increment "sequence" column based on alphabetical order of another column

Assume the following schema:

text|sequence
----|--------
Foo |       1
Bar |       2
Baz |       3

How would I UPDATE the sequence column so it incremented starting at 1 with no gaps based on the alphabetical order of the text column?

The resulting data would look like:

text|sequence
----|--------
Foo |       3
Bar |       1
Baz |       2

Upvotes: 2

Views: 1676

Answers (2)

Casey
Casey

Reputation: 91

Try this

UPDATE A
SET A.Sequence = A.newSequence
FROM (SELECT Text, Sequence, ROW_NUMBER() OVER (ORDER BY sequence) newSequence
            FROM TABLEA) A

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

UPDATE A 
SET A.sequene = B.sequence 
FROM tableA A 
INNER JOIN (SELECT text, ROW_NUMBER() OVER (ORDER BY text) sequence 
            FROM tableA 
           ) AS B ON A.text = B.text

Upvotes: 3

Related Questions