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

 

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s