asked on

SQL how to update a field with sequential number but sorted alphabetically

Hi there,
I would like to update a field in a table with a sequential number. I can do this using:

DECLARE @seqNum int
SET @seqNum = 0
UPDATE
AnalysisTable
SET
@seqNum = myAnalysisCode = @seqNum + 1
Where MyAnalysisType = whateverIchoose

This works fine, BUT I want to sort my selection alphabetically before adding the sequential code - eg I have data:

cat toys large
dog toys small
cat toys medium
hamster wheels
Dog chews

I want to add an analysis code so I get
SequentialCode
cat toys large 1
dog toys small 4
cat toys medium 2
hamster wheels 5
Dog chews 3

Can this be done?

Vikas Garg

You can use Row_Number function

Row_Number () over (order by your column with text data)

then you will not need to udpate

alexking

ASKER

I actually need to update the field - they are all set to zero currently and it needs to be updated with a sequntial number, alphabetically.

Jim Horn

To illustrate, copy-paste the below T-SQL into SSMS and execute

-- Create a temp table just to illustrate
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp
GO

CREATE TABLE #tmp (name varchar(50)) 
GO

-- Create the test table
INSERT INTO #tmp (name) 
VALUES ('cat toys large'), ('dog toys small'),('cat toys medium'),('hamster wheels'), ('Dog chews')

-- Return a set with the ranking column, don't actually change the table
SELECT name, ROW_NUMBER() OVER (ORDER BY name) as rank_order
FROM #tmp

-- Change the table to include this number
ALTER TABLE #tmp
ADD rank_order int 
GO

UPDATE #tmp
SET rank_order = ro.rank_order 
FROM #tmp
   JOIN (SELECT name, ROW_NUMBER() OVER (ORDER BY name) as rank_order FROM #tmp) ro ON #tmp.name = ro.name

SELECT * FROM #tmp

Open in new window

Deepak Chauhan

You can use this query to update the table. replace actual column and table name.

update <tablename> set id=t.rno from (select ROW_NUMBER() over(order by name) as rno, name, id from <table anme>)t
where ty.name=t.name

ASKER CERTIFIED SOLUTION

Vikas Garg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alexking

ASKER

This method worked a treat - i did have some problems with some of the others. Thank you for the help! I have not used CTEs before - they are very useful!

Jim Horn

alexking - Let me know what problems you had with mine, as it looked pretty good when I wrote it and matched what I understood were your requirements.