Can IDENTITY be reset? 1

[cathyg18]

I want to have a counter column in a temp table that resets to 0 when account number changes. It looks like IDENTITY gets me part of the way there, but won't reset when account numbers change.

Example:
Account Color Counter
123 Blue 1
123 Yellow 2
123 Orange 3
456 Red 1
789 Blue 1
789 Yellow 2
789 Purple 3
789 Green 4

I am using SQL 2005.

Thanks!
Cathy

 

[RiverGuy]

Just created a normalized data model. Create an Accounts table. Store each distinct account number (and possibly other information such as account name) in the Accounts table, and create an identity column in that table. The, you can join your current table to that accounts table, and return the identity column in your query.

 

[bborissov]

--- Preparing test data
DECLARE @Test TABLE (Account int, Color varchar(200))
INSERT INTO @Test VALUES (123,'Blue')
INSERT INTO @Test VALUES (123,'Yellow')
INSERT INTO @Test VALUES (123,'Orange')
INSERT INTO @Test VALUES (456,'Red')
INSERT INTO @Test VALUES (789,'Blue')
INSERT INTO @Test VALUES (789,'Yellow')
INSERT INTO @Test VALUES (789,'Purple')
INSERT INTO @Test VALUES (789,'Green')
---- end of it

SELECT Account,
       Color,
       ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Color) AS Counting
FROM @Test

Is this what you want?
If so, just change FROM @Test to FROM YourRealTableName


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

Boris beat me by a minute.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cathyg18]

You guys are THE BEST!! The row_number over partition select worked perfectly.

My poor bruised head that has been banging against the wall thanks you, and I thank you too.

Cathy