nth nth - 1 month ago 5
SQL Question

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

Comments