SQL query for non-duplicate records

Question

I am trying to create a query that will return all non-dual (unique) records in a table. The request will require several fields to determine duplicate entries.

For example, if the table has the following fields; PKID, ClientID, Name, AcctNo, OrderDate, Charge, I would like to use the AcctNo, OrderDate and Charge fields to search for 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 request should 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 tried using SELECT DISTINCT, but this does not work because it saves one of the duplicate records as a result. I also tried using HAVING COUNT = 1, but it returns all records.

Thanks for the help.

Answer 1

HAVING COUNT(*) = 1 will work if you only include the fields in the GROUP BY that you use to find unique entries. (i.e. not PKID , but you can use MAX or MIN to return this, since you will only have one entry for each group in the result set.)

Answer 2

 SELECT MAX(PKID) AS PKID , MAX(ClientID) AS ClientID, MAX(Name) AS Name , AcctNo , OrderDate , Charge FROM T GROUP BY AcctNo , OrderDate, Charge HAVING COUNT(*) = 1 

or

 SELECT PKID , ClientID , Name , AcctNo , OrderDate , Charge FROM YourTable t1 WHERE 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 ) 

Answer 3

Just add:

 GROUP BY AcctNo, OrderDate, Charge HAVING COUNT(1) = 1 

GROUP BY groups all rows with the same AcctNo, OrderDate and Charge together, then HAVING COUNT(1) = 1 shows only rows in which there was only one predecessor.

Answer 4

Thanks to kekekela for pushing in the right direction.

Here is the query that triggered the result I wanted:

 SELECT AcctNo, OrderDate, Charge FROM Table1 GROUP BY AcctNo, OrderDate, Charge HAVING (COUNT(AcctNo) = 1) AND (COUNT(OrderDate) = 1) AND (COUNT(Charge) = 1); 

Or more simplified based on the Gus example:

 SELECT AcctNo, OrderDate, Charge FROM Table1 GROUP BY AcctNo, OrderDate, Charge HAVING COUNT(1) = 1; 

Answer 5

You can simply leave a PKID to return all entries:

 SELECT DISTINCT ClientID , Name , AcctNo , OrderDate , Charge FROM table; 

---

Note: This is slightly different from what you are asking. It returns a unique set, removing one non-unique field.
In your example, you are asking for non-duplicate returns.

I could only see that your example is useful if you are trying to clean the table by retrieving the "good" records.

Answer 6

At first you can identify non-unique records, and then check these records not in this set - for example,

 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 internal query allows you to tinker with the criteria of "equality" - add the required number of columns for verification. Of course, it becomes much more interesting without this primary key :) In such cases, I usually create one

Ketyl

Answer 7

  SELECT GMPS.gen.ProductDetail.PaperType, GMPS.gen.ProductDetail.Size FROM GMPS.gen.ProductDetail GROUP BY GMPS.gen.ProductDetail.PaperType, GMPS.gen.ProductDetail.Size HAVING COUNT(1) = 1;