Clever Geek Handbook

SQL Find Possible Duplicates

I need SQL code that will identify possible duplicates in a table. Let's say my table has 4 columns:

  • Identifier (primary key)

  • Date1

  • Date2

  • GroupID

(Date1, Date2, GroupID) form a unique key.

This table is filled with blocks of data at a time, and it often happens that a new block is loaded containing several records that are already there. It's fine as long as the unique key catches them. Unfortunately, sometimes Date1 is empty (or at least "1900/01/01") with either the first load or the later.

So what I need is to determine where the combination (Date2, GroupID) appears more than once and where for one records Date1 = '1900/01/01'

thank

Charles

+2


source to share


7 replies


bkm looks like it, but internal selection may not work well in some databases. This is more straight forward:



select t1.* from 
t as t1 left join t as t2 
on (t1.date2=t2.date2 and t1.groupid=t2.groupid)
where t1.id != t2.id and (t1.date1='1900/01/01' or t2.date2='1900/01/01')
+2


source


You can identify duplicates (date2, GroupID) with

Select date2,GroupID
from t
group by (date2,GroupID)
having count(*) >1

Use this to identify records in the main table that are duplicated:



Select *
from t
where date1='1900/01/01'
and (date2,groupID) = (Select date2,GroupID
                       from t
                       group by (date2,GroupID)
                       having count(*) >1)

NOTE. Since Date1, Date2, GroupID form a unique key, check if your design is correct so that Date1 is NULL. You might have a genuine case where Date 1 is different for two strings, whereas (date2, GroupID) is the same

+1


source


If I understand correctly, you are looking for a group of IDs that have the same GroupID and Date2, one Date1 event other than 1900/01/01, and all the other Date1 events are 1900/01/01.

If I understood correctly, here is the request for you:

SELECT T.ID 
FROM Table T1
WHERE 

(T1.GroupID, T1.Date2) IN
    (SELECT T2.GroupID, T2.Date2
    WHERE T2.Date1 = '1900/01/01' OR
        T2.Date IS NULL
    GROUP BY T2.GroupID, T2.Date2)

AND 

1 >= 
(
    SELECT COUNT(*) 
    FROM TABLE T3
    WHERE NOT (T3.Date1 = '1900/01/01') 
    AND NOT (T3.Date1 IS NULL)
    AND T3.GroupID = T1.GroupID
    AND T3.Date2 = T1.Date2
)

Hope it helps.

+1


source


There may be a verification limitation.

Something along the lines select count(*) where date1 = '1900/01/01' and date2 = @date2 and groupid = @groupid

.

Just need to see if you can do it in a table level constraint ....

0


source


Besides having a PRIMARY KEY field defined on the table, you can also add other UNIQUE constraints to accomplish the same thing you are asking for. They will confirm that a particular column or set of columns has a unique value in the table.

Note the entry in the MySQL manual for an example:

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

0


source 


select * from table a
join (
select Date2, GroupID, Count(*)
from table
group by Date2, GroupID
having count(*) > 1
) b on (a.Date2 = b.Date2 and a.GroupID = b.GroupID)
where a.Date1 = '1900/01/01'
0


source


This is the easiest way to do it:

SELECT DISTINCT t1.*
FROM t t1 JOIN t t2 USING (date2, groupid)
WHERE t1.date1 = '1900/01/01';

Don't need to use GROUP BY

, which doesn't work well on some brands of database.

0


source






More articles:


All Articles