counting duplicates

[Dinesh.T.K]

Jane,

SELECT field1,count(field1) 'Counter'
FROM <tgablename>
GROUP BY field1

--
Dinesh.
SQL Server FAQ at
[url]http://www.tkdinesh.com[/url]

"Jane K." <Jane@123.com> wrote in message
news:00ea01c33f24$e5155960$a301280a@phx.gbl...

> How can I set up a counter so that whenever there is a
> duplicate records, the counter will increase by one. In
> other words, I would like to find out how to make the list
> looks like this:
>
> Field 1: Counter:
> A 1
> B 1
> C 1
> C 2
> D 1
> E 1
>
> Thank you in advance to answer my question.
>
>
>

[John Bell]

Hi

You don't give the DDL for the table, but if you had a separate Unique Index
or Primary Key field(s) say PKFld the following sql may work (untested)

SELECT b.Field1, ( SELECT COUNT(*) FROM MyTable A
WHERE a.PkFld <= b.PKFld AND a.Field1 =
b.Field1 ) AS Counter
FROM MyTable B
Order by b.Field1, b.PkFld

John


"Jane K." <Jane@123.com> wrote in message
news:00ea01c33f24$e5155960$a301280a@phx.gbl...

> How can I set up a counter so that whenever there is a
> duplicate records, the counter will increase by one. In
> other words, I would like to find out how to make the list
> looks like this:
>
> Field 1: Counter:
> A 1
> B 1
> C 1
> C 2
> D 1
> E 1
>
> Thank you in advance to answer my question.
>
>
>