On Finding duplicate values in column of SQL Table, Increment a COUNT column by 1

Question

While going through sql columns, if we find duplicate values then we need incrementing a count starting with 1.

Any Value that matches the original value found in column "Filenum".

It should look like this on the output.

-------------------------------
| FILENUM coulmn | num column | 
| a              |          1 |
| a              |          2 |
| a              |          3 |
| b              |          1 |
| b              |          2 |
| c              |          1 |
| d              |          1 |
| a              |          4 |
| b              |          3 | 
-------------------------------

Any solution that you can suggest..It will be even better if funcions like row_number() can be avoided..Thanks in adv

Without furthor information e.g. ID (identity) you won't be able to update duplicates. — bummi, Jan 19 '13 at 07:34
I know one solution with Row_number..but will work post SQL Server2005.... SELECT FILENUM, num = ROW_NUMBER() OVER (PARTITION BY FILENUM Order By FILENUM) FROM table1 — Haimanta, Jan 19 '13 at 08:04

score 1 · Answer 1 · answered Nov 25 '13 at 12:08

You can use a Sequence table to get an incrementing number against each duplicate. A sequence table has a single field with an incrementing number up to whatever maximum value you think you will need.

I usually create a sequence table with one million rows for general use, in your case you will only need as many rows as there are duplicates; for this example we'll just add 100 rows:

CREATE TABLE dbo.Sequence
(
    seq_nbr INT NOT NULL,
    CONSTRAINT PK$Sequence
        PRIMARY KEY CLUSTERED (seq_nbr)
        WITH FILLFACTOR = 100,
    CONSTRAINT Sequence$seq_nbr$positive CHECK (seq_nbr > 0)
)
GO
INSERT INTO dbo.Sequence (seq_nbr)
SELECT ten * 10 + unit + 1
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Units(unit)
     CROSS JOIN
     (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Tens(ten)

Using the sequence table you cross join it with your table to get the duplicate counter:

SELECT  letter
       ,seq_nbr 
FROM    dbo.Sequence
       ,dbo.Duplicates
GROUP BY letter
       ,seq_nbr 
HAVING  seq_nbr <= COUNT(letter)

Giving the result:

letter seq_nbr 
------ -------
a            1
a            2
a            3
a            4
b            1
b            2
b            3
c            1
d            1

Here's a SQL Fiddle showing the result.

bonCodigo · Answer 2 · 2013-01-19T15:55:20.347

Given that you do not want to use Row_Number() or Dense_Rank() in SQL Server, then you may need to

pull current data into a temp table with autoincrement
then get the running total for each value from there

Else this could be manipulated into SQL server:

set @rno:=0; set @names:='';
select @rno:=case when @names<>names then  1
else @rno+1 end as rno, @names:=names as names
from demo order by names;

Expected output:

rno   names
1       a             -- '' <> a , rno = 1
2       a             -- a = a, rno = 1 + 1 so on...
3       a
4       a
1       b
2       b
1       c
1       d

Tried out on SQLFIDDLE and it works :)

SQLFIDDLE DEMO

@haimanta take a look at this query please. You may try to implement the logic in SQL server and comment if it works =) — bonCodigo, Jan 19 '13 at 10:57

On Finding duplicate values in column of SQL Table, Increment a COUNT column by 1

2 Answers2

SQLFIDDLE DEMO