Auto number and reset based on data value in a column

[Jason Wilson]

I have a table with data like the following:
AAA
AAA
AAA
BBB
BBB
CCC

I would like to append an auto number and reset/reseed the autonumber for
each unique value. I would like the data to look like this:
AAA_1
AAA_2
AAA_3
BBB_1
BBB_2
CCC_1

Any help would be greatly appreciated! Sounds like it should be simple
enough, but I am stumped currently.

[Peso]

SELECT Col1, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS Running
FROM Table1

"Jason Wilson" <Jason...@discussions.microsoft.com> wrote in message
news:B1011124-16F4-4A26...@microsoft.com...

[John Bell]

"Jason Wilson" <Jason...@discussions.microsoft.com> wrote in message
news:B1011124-16F4-4A26...@microsoft.com...

Hi

You don't say what version of SQL Server you are using, but the ROW_NUMBER
function can do this if you partition over you values.
See http://msdn.microsoft.com/en-us/library/ms186734.aspx e.g.

SELECT [Id], [Value], ROW_NUMBER() OVER (PARTITION BY [Value] ORDER BY [Id]
DESC) AS [ROWNUM], [VALUE] + '_' + CAST(ROW_NUMBER() OVER (PARTITION BY
[Value] ORDER BY [Id] DESC) AS VARCHAR(10)) AS [COMPOSITE]
FROM (
SELECT 1 AS [Id], 'AAA' AS [Value]
UNION ALL SELECT 2 , 'AAA'
UNION ALL SELECT 3 , 'AAA'
UNION ALL SELECT 4 , 'BBB'
UNION ALL SELECT 5 , 'BBB'
UNION ALL SELECT 6 , 'CCC' ) D

If you can do this when retrieving the information, there will be no need to
store it. Trying to store composite keys could be a maintenance nightmare.

John

[douglubey]

Thanks for the post:
Doug Lubey of Louisiana
www.douglubey.com

search-engine reference for the next person
------------------------------------------------
sql row column counter for specific coloumn value
sql 2008 row counter by column value
sql 2008 increment row counter by column value
sql 2008 reset row counter by column value
sql 2008 reset row count by column value
microsoft sql reset row count using a column value
microsoft sql paging reset row count using a column value
tsql reset row count using a column value
tsql count rows
tsql reset rowcount from column value
microsoft transact sql select statement with rowcount
select sql change row count on new value
tsql reset row count
sql select dynamically create number columnn
sql select dynamically create number columnn with out using cursor
sql select number a column
sql select reset row number based on a column value
sql 2005 create number column
sql 2005 ROWCOUNT()
transactional sql rank()
microsoft sql statement to dynammically reset column counts

John Bell wrote:

Re: Auto number and reset based on data value in a column
17-Aug-09

Hi

You do not say what version of SQL Server you are using, but the ROW_NUMBER

function can do this if you partition over you values.
See http://msdn.microsoft.com/en-us/library/ms186734.aspx e.g.

SELECT [Id], [Value], ROW_NUMBER() OVER (PARTITION BY [Value] ORDER BY [Id]
DESC) AS [ROWNUM], [VALUE] + '_' + CAST(ROW_NUMBER() OVER (PARTITION BY
[Value] ORDER BY [Id] DESC) AS VARCHAR(10)) AS [COMPOSITE]
FROM (
SELECT 1 AS [Id], 'AAA' AS [Value]
UNION ALL SELECT 2 , 'AAA'
UNION ALL SELECT 3 , 'AAA'
UNION ALL SELECT 4 , 'BBB'
UNION ALL SELECT 5 , 'BBB'
UNION ALL SELECT 6 , 'CCC' ) D

If you can do this when retrieving the information, there will be no need to
store it. Trying to store composite keys could be a maintenance nightmare.

John

Previous Posts In This Thread:

On Monday, August 17, 2009 3:31 PM
Jason Wilson wrote:

Auto number and reset based on data value in a column

I have a table with data like the following:
AAA
AAA
AAA
BBB
BBB
CCC

I would like to append an auto number and reset/reseed the autonumber for
each unique value. I would like the data to look like this:
AAA_1
AAA_2
AAA_3
BBB_1
BBB_2
CCC_1

Any help would be greatly appreciated! Sounds like it should be simple
enough, but I am stumped currently.

On Monday, August 17, 2009 4:13 PM
Peso wrote:

SELECT Col1, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS
SELECT Col1, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS Running
FROM Table1

On Monday, August 17, 2009 5:07 PM
John Bell wrote:

Re: Auto number and reset based on data value in a column
Hi

You do not say what version of SQL Server you are using, but the ROW_NUMBER

function can do this if you partition over you values.
See http://msdn.microsoft.com/en-us/library/ms186734.aspx e.g.

SELECT [Id], [Value], ROW_NUMBER() OVER (PARTITION BY [Value] ORDER BY [Id]
DESC) AS [ROWNUM], [VALUE] + '_' + CAST(ROW_NUMBER() OVER (PARTITION BY
[Value] ORDER BY [Id] DESC) AS VARCHAR(10)) AS [COMPOSITE]
FROM (
SELECT 1 AS [Id], 'AAA' AS [Value]
UNION ALL SELECT 2 , 'AAA'
UNION ALL SELECT 3 , 'AAA'
UNION ALL SELECT 4 , 'BBB'
UNION ALL SELECT 5 , 'BBB'
UNION ALL SELECT 6 , 'CCC' ) D

If you can do this when retrieving the information, there will be no need to
store it. Trying to store composite keys could be a maintenance nightmare.

John

Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Circular Progress Indicator
http://www.eggheadcafe.com/tutorials/aspnet/4d89b4cb-ba59-4362-ab0a-cc047643fd42/wpf-circular-progress-ind.aspx

[douglubey]

SQL CONSECUTIVE NUMBERING COLUMNS BASED ON A VALUE IN A ROW
SQL CONSECUTIVE NUMBERING A COLUMN BASED ON A VALUE
SQL RESET COLUMN VALUE TO 1 AGAIN WHEN VALUE CHANGES IN PREVIOUS RECORD ON GROUPING CLAUSE

HERE IS SOME OF THE CODE i USED:
PageCategoryRank = rank() OVER (ORDER BY count([InternetActivityID]) desc,[PageCategory]),

BASED ON THE RANKING THE NUMBERING OF THE RANK COLUMN WOULD CHANGE BACK TO 1.

Doug Lubey wrote:

PARTITION: finally found the auto-increment column which is dynamically computed -thanks
05-Apr-10

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice

File-Based Cache for Web and non-Web Apps plus Extend ASP.NET 4.0 OutputCacheProvider
http://www.eggheadcafe.com/tutorials/aspnet/56161e9e-7fa3-48e8-9dfe-9f7a28f4d58e/filebased-cache-for-web.aspx