INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...Keep up the very good job that you and your team are doing. This site has replaced my morning cup of tea as a must have/do!!!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

SQLUpdate Help

Read More Threads Like This One

cs2009 (Programmer)

8 May 12 11:47

I have data in a table like this:

SortKey                                 SortKeyNum
01-01-176DC HEAD 5.875"21 5/8''             Null
01-01-176DC HEAD 5.875"27 5/8''             Null
01-01-176DC HEAD 5.875"27 5/8''             Null
01-03-137DC SILL 6.0625" 23''                 Null
01-03-137DC SILL 6.0625" 23''                 Null
03-44-141XX LVR  6.0625" 47 1/2''           Null

I want to update the SortKeyNum field by incrementing it by 1 whenever the SortKey field changes.  So, the SortKeyNum field should look like this:

SortKey                                SortKeyNum
01-01-176DC HEAD 5.875"21 5/8''             1
01-01-176DC HEAD 5.875"27 5/8''             1
01-01-176DC HEAD 5.875"27 5/8''             1
01-03-137DC SILL 6.0625" 23''                 2
01-03-137DC SILL 6.0625" 23''                 2
03-44-141XX LVR  6.0625" 47 1/2''           3

djj55 (Programmer)

8 May 12 12:21

Suggest using a cte to find the unique sortkey and assigning a sortkeynum to that (the cte) then using it to update your table.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

markros (Programmer)

8 May 12 13:53

Use DENSE_RANK() function, e.g.

CODE

select SortKey, DENSE_RANK() OVER (ORDER BY SortKey) as SortKeyNum

PluralSight Learning Library

djj55 (Programmer)

8 May 12 14:01

markros, I had forgotten about DENSE_RANK(). That would be a better way.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming