SQL query for non duplicate records SQL query for non duplicate records sql sql

SQL query for non duplicate records

HAVING COUNT(*) = 1 will work if you only include the fields in the GROUP BY that you're using to find the unique records. (i.e. not PKID, but you can use MAX or MIN to return that since you'll only have one record per group in the results set.)

SELECT   MAX(PKID)     AS PKID    ,         MAX(ClientID) AS ClientID,         MAX(Name)     AS Name    ,         AcctNo                   ,         OrderDate                ,         ChargeFROM     TGROUP BY AcctNo   ,         OrderDate,         ChargeHAVING   COUNT(*) = 1


SELECT PKID      ,       ClientID  ,       Name      ,       AcctNo    ,       OrderDate ,       ChargeFROM   YourTable t1WHERE  NOT EXISTS       (SELECT *       FROM    YourTable t2       WHERE   t1.PKID     <> t2.PKID       AND     t1.AcctNo    = t2.AcctNo       AND     t1.OrderDate = t2.OrderDate       AND     t1.Charge    = t2.Charge       )

Simply add:

GROUP BY AcctNo, OrderDate, ChargeHAVING COUNT(1) = 1

The GROUP BY groups all rows with the same AcctNo, OrderDate and Charge together,then the HAVING COUNT(1) = 1 shows only the rows where there was just 1 progenitor.