Re-Order integer field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Re-Order integer field

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2010-03-17 : 11:44:52

Hi,

i have the following data:

Account Seq
123 1
123 3
123 4
456 1
456 1
456 2

what i need to do is re-order the Seq column to remove any gaps or duplicates.

I need the above to be like this:
Account Seq
123 1
123 2
123 3
456 1
456 2
456 3

Any ideas??

thanks
scott

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 11:50:19

[code]SELECT IDENTITY(int,1,1) AS ID,Account INTO #Temp
FROM Table

SELECT r.Account,r.Seq
FROM
(
SELECT t.Account,
(SELECT COUNT(*) FROM #Temp WHERE Account=t.Account AND ID <t.ID) + 1 AS Seq
FROM #Temp t
)r
ORDER BY r.Account,r.Seq
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 12:02:06

Is this SQL 2000? (Its in the SQL 2000 forum ...) as there are easier ways in SQL 2005 onwards ...

scabral7
Yak Posting Veteran

57 Posts

Posted - 2010-03-17 : 13:52:13

yes, sql 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources