SQL query for non duplicate records

Question

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 1

You could determine the non-unique records first, and then test for those records not in that set - like this

select * from mytable where pkid not in
(select t1.pkid 
from mytable t1 inner join mytable t2
on t1.pkid <> t2.pkid
and t1.acctno = t2.acctno
and t1.orderdate = t2.orderdate
and t1.charge = t2.charge)

the last part of the inner query lets you fiddle with the criteria for "equality" - add the required number of columns to test. Of course, this gets a lot more interesting without that primary key :) In such cases I usually end up creating one

Ketil