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

Many to One with Max Date Query?

I have a master table that has all my accounts in it.
In a 2nd table I have update notes per each account, so there are
multiple notes entries for each single ID account in teh first table.

How can I get the OLDEST dated entry in teh second table, for every
account that's had a not entered into if by joining on the ID of the
first table?

This has been driving me nuts, does that make sense to you?

Here:

Table 1 FIELDS
ACCOUNTID

TABLE 2 FIELDS
NOTEID
ACCOUNTID (Foreign Key)
DATE_ENTERED

I need to join those two tables on the ACCOUNTID, but ONLY show the most
recent date for the record I pull from Table 2, since it's got multiple
entries in table 2, it's screwing my query up. Any help/ideas?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!SELECT T1.accountid, T2.noteid, T2.date_entered
FROM Table1 AS T1
JOIN Table2 AS T2
ON T1.accountid = T2.accountid
AND T2.date_entered =
(SELECT MIN(date_entered)
FROM Table2
WHERE accountid = T2.accountid)

--
David Portas
SQL Server MVP
--

Management Studio support for Foundation Server Source Control

2nd Post on this as I didi not have a NoSpam alias!!
Does the SQL 2005 Management Studio support source Control from Team
Foundation Server. All the documentation refers to VSS.
I have the TFS client installed and is working with Visual Studio but is not
a visable option in SQL Management Studio.
Does any one know of a TFS Plug in for SQL 2005 Managament Studio?
Hi Peter,
Thanks for your posting.
From your descriptions, I understood you would like to know whether it is
possible to use Team Foundation Server for SQL Server 2005 source control.
If I have misunderstood your concern, please feel free to point it out.
First of all, since TFS has not been released, we do not have much document
on this.
Based on my knowledge, unfortunately, TFS cannot work with SQL Server 2005
now. You may also post your question in the newsgroup below for more
information
MSDN - Visual Studio Team System
http://forums.microsoft.com/MSDN/def...pid=5&siteid=1
Last but not the least, other communities might share their invaluable
experience here.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.