Mark duplicate records in a Select statement ?

I have a requirement to mark duplicate records when I pull them from the database.

However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.

The code I have below creates a column called Dupes but marks all the duplicates - including the first one.

Is there a way to only mark the 2nd, 3rd, 4th etc record ?

SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,

Dupes = (select count(id)
from CallLogs
where (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)
OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
AND DealerId= 'hdsh'
AND CSRStatus IS NULL
and datediff(d, logdate, getdate()) <= 21),

FROM CallLogs cl
left Join CallStatus cs on cs.Id = cl.CSRstatus
left Join CallStatus cs2 on cs2.Id = cl.Customerstatus
Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)
And DealerId='hdsh'
And Logdate Between '08/01/2007' And '08/31/2007'

Can't you just decrement the 'count' statement by 1 before assigning it to Dupes?

1Dupes = (SELECTcount(id)
2FROM CallLogs
3WHERE(CustomerHomePhone !=''AND cl.CustomerHomePhone = CustomerHomePhone)
4 OR (CustomerBusinessPhone !=''AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
5AND DealerId='hdsh'
6 AND CSRStatusISNULL
7 ANDdatediff(d, logdate,getdate()) <= 21))- 1

That way it just accounts for itself by subtracting 1 from the total 

|||

The problem is that both records get the same Dupe number.

I need the 2nd "dupe" to be marked as 2, the 3rd "dupe" to be marked as 3 etc

Manipulate duplicate rows

I have two of the exact same rows, but I want to change one field of one of
the rows. How do I do this?
i.e.
table_a
group name revenue
A jim 20
A jim 20
I want to change one jim to ken.
I tried
update table_a
set name = 'ken'
from (select top 1 * from table_a) as t1
where table_a.group = t1.group
but this updates both records
On Fri, 15 Apr 2005 09:59:02 -0700, bamboo wrote:

> I have two of the exact same rows, but I want to change one field of one of
>the rows. How do I do this?
>i.e.
>table_a
>group name revenue
>A jim 20
>A jim 20
>I want to change one jim to ken.
>I tried
>update table_a
>set name = 'ken'
>from (select top 1 * from table_a) as t1
>where table_a.group = t1.group
>but this updates both records
Hi bamboo,
This really should not have happened - each table in a database should
always have a primary key. You have just found out why: there is no way
for SQL Server to distinguish one row from the other, so all operations
will affect both or none.
Here's a link to an article that discusses some techniques to clean up
the mess: http://www.aspfaq.com/show.asp?id=2431.
And here's a link to an article that explains what you can do to prevent
this from happening again: http://www.aspfaq.com/show.asp?id=2509.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)