SQL query for non duplicate records

I’m attempting to build a query that will return all non duplicate (unique) records in a table. The query will need to use multiple fields to determine if the records are duplicate.

For example, if a table has the following fields; PKID, ClientID, Name, AcctNo, OrderDate, Charge, I’d like to use the AcctNo, OrderDate and Charge fields to find unique records.

Table

PKID-----ClientID-----Name-----AcctNo-----OrderDate-----Charge
1        JX100        John     12345      9/9/2010      $100.00
2        JX220        Mark     55567      9/9/2010       $23.00
3        JX690        Matt     89899      9/9/2010      $218.00
4        JX100        John     12345      9/9/2010      $100.00

The result of the query would need to be:

PKID-----ClientID-----Name-----AcctNo-----OrderDate-----Charge
2        JX220        Mark     55567      9/9/2010       $23.00
3        JX690        Matt     89899      9/9/2010      $218.00

I’ve tried using SELECT DISTINCT, but that doesn’t work because it keeps one of the duplicate records in the result. I’ve also tried using HAVING COUNT = 1, but that returns all records.

Thanks for the help.

Answer

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.)