TSQL- generate a sequence number for duplicate records

Question

Using SQL Server 2000, consider a source table with more than 400,000 records.

The task is to select each regno entry with an incrementing on-the-fly rowid or sequence number for those with duplicates or multiple entries. For those which do NOT have duplicate entries in the source table, the rowid should simply be null.

Here's an example of the desired output:

    regno   rowid
    100      1
    100      2
    100      3
    200      null
    300      4
    300      5
    400      null
    500      null
    600      6
    600      7

Question: What query would do the desired sequence incrementing using TSQL in SQL Server 2000?

Answer 1

The query to extract the non-unique records would be

select regno,count(*) from table group by regno having count(*) > 1

I don't know enough about MSSQL to tell you how to generate an incrementing sequence number to update the records that match the query.

Answer 2

If my comment is correct (600 should be 6,7) then have a look at this

DECLARE @Table TABLE(
     regno INT,
     rowid INT
)

INSERT INTO @Table (regno,rowid) SELECT 100, NULL
INSERT INTO @Table (regno,rowid) SELECT 100, NULL
INSERT INTO @Table (regno,rowid) SELECT 100, NULL
INSERT INTO @Table (regno,rowid) SELECT 200, NULL
INSERT INTO @Table (regno,rowid) SELECT 300, NULL
INSERT INTO @Table (regno,rowid) SELECT 300, NULL
INSERT INTO @Table (regno,rowid) SELECT 400, NULL
INSERT INTO @Table (regno,rowid) SELECT 500, NULL
INSERT INTO @Table (regno,rowid) SELECT 600, NULL
INSERT INTO @Table (regno,rowid) SELECT 600, NULL

DECLARE @TempTable TABLE(
     ID INT IDENTITY(1,1),
     regno INT
)

INSERT INTO @TempTable (regno)
SELECT  regno
FROM    @Table

SELECT  regno,
     CASE 
      WHEN (SELECT COUNT(1) FROM @TempTable WHERE regno = t.regno) = 1 
       THEN NULL 
      ELSE (SELECT COUNT(1) FROM @TempTable WHERE regno = t.regno) - (SELECT COUNT(1) FROM @TempTable WHERE regno = t.regno AND ID > t.ID) +
      (SELECT COUNT(1) FROM @TempTable WHERE regno < t.regno AND regno IN (SELECT regno FROM @TempTable GROUP BY regno having COUNT(1) > 1))
     END Val  
FROM    @TempTable t

Answer 3

Without a temp table:

DECLARE @Table TABLE(
        regno INT
)

INSERT INTO @Table (regno) SELECT 100
INSERT INTO @Table (regno) SELECT 100
INSERT INTO @Table (regno) SELECT 100
INSERT INTO @Table (regno) SELECT 200
INSERT INTO @Table (regno) SELECT 300
INSERT INTO @Table (regno) SELECT 300
INSERT INTO @Table (regno) SELECT 400
INSERT INTO @Table (regno) SELECT 500
INSERT INTO @Table (regno) SELECT 600
INSERT INTO @Table (regno) SELECT 600

select regno, null as rowid from @Table group by regno having count(*) = 1
union 
select regno, row_number() OVER (ORDER BY a.regno) as rowid
   from @table a
   where regno in (select regno from @table group by regno having count(*) > 1)

regno       rowid
----------- --------------------
100         1
100         2
100         3
200         NULL
300         4
300         5
400         NULL
500         NULL
600         6
600         7

Oops - did not see that you want to do this in SQL 2000 until after posting this ... ignore my query please. In SQL 2000 you need a temp table to generate the sequence.