SQLUpdate Help 1

[cs2009]

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]

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]

Use DENSE_RANK() function, e.g.

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

PluralSight Learning Library

 

[djj55]

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!