sql COUNT question

How can I modify this query to return results that exclude all records that have a duplicate DocNum?

SELECT IdEntry, DocNum, CardCode, QUOTENAME(CardName,'"'), 
Convert(Decimal(10,2),PayAmount), 开发者_运维技巧Convert(Decimal(10,2),InvPayAmnt), 
CONVERT(VARCHAR(10), T5.PmntDate,101), NumAtCard, PymMeth, ObjType
FROM DELAWARE.dbo.PWZ3
INNER JOIN OPWZ T5 ON T5.IdNumber = IdEntry
WHERE T5.PmntDate = '3/10/2011' 
AND T5.Canceled = 'N' 
AND Checked = 'Y'

Try

WITH
    dups (DocNum)
    AS (
        SELECT DocNum
        FROM DELAWARE.dbo.PWZ3
        GROUP BY DocNum
        HAVING COUNT(1) > 1
    )
SELECT PWZ3.IdEntry, PWZ3.DocNum, PWZ3.CardCode, QUOTENAME(CardName,'"'), 
Convert(Decimal(10,2),PayAmount), Convert(Decimal(10,2),InvPayAmnt), 
CONVERT(VARCHAR(10), T5.PmntDate,101), NumAtCard, PymMeth, ObjType
FROM DELAWARE.dbo.PWZ3 PWZ3
INNER JOIN OPWZ T5 ON T5.IdNumber = PWZ3.IdEntry
LEFT JOIN Dups ON DUPS.DocNum = PWZ3.DocNum
WHERE T5.PmntDate = '3/10/2011' 
AND T5.Canceled = 'N' 
AND Checked = 'Y'
AND Dups.DocNum is null

(I might not have all the column aliases right)

I'm not sure I understood all the query, but doesn't a simple

 SELECT ... GROUP BY docNum

suffice?

SELECT IdEntry, DocNum, CardCode, QUOTENAME(CardName,'"'), 
Convert(Decimal(10,2),PayAmount), Convert(Decimal(10,2),InvPayAmnt), 
CONVERT(VARCHAR(10), T5.PmntDate,101), NumAtCard, PymMeth, ObjType

FROM DELAWARE.dbo.PWZ3
INNER JOIN OPWZ T5 ON T5.IdNumber = IdEntry

JOIN (SELECT min(IdEntry) as minIdEntry from DELAWARE.dbo.PWZ3 group by DocNum) tmp on DELAWARE.dbo.PWZ3.IdEntry = minIdEntry

WHERE T5.PmntDate = '3/10/2011' 
AND T5.Canceled = 'N' 
AND Checked = 'Y'