Find duplicate items in an Oracle SQL column

Question

I searched for Find duplicate entries in a column and Oracle: find duplicate rows in a selected query , but didn't seem to get any response ...

I have some data that look like

columnA columnB columnC 1111111 emailID1 true 1111111 emailID2 false 1111222 emailID3 true 1111339 emailID4 true 2384398 emailID5 true 

I would like to display only this column, which has the same values ​​in column A, but may be different in column B and / or C:

 columnA columnB columnC 1111111 emailID1 true 1111111 emailID2 false 

Using availability> 1 really doesn't look like this, any ideas? Thanks.

Answer 1

Using having count(*) > 1 is only one half of the puzzle. The other half gets the corresponding lines.

You can do it as follows:

 SELECT * FROM MyTable WHERE ColumnA IN ( SELECT ColumnA FROM MyTable GROUP BY ColumnA HAVING COUNT(*) > 1 ) 

Answer 2

Try the following:

 SELECT t.* FROM (SELECT ColumnA FROM MyTable GROUP BY ColumnA HAVING COUNT(*) > 1) dups JOIN MyTable t ON t.ColumnA = dups.ColumnA 

This will also scale well if the index is on ColumnA :

 create index MyTable_ColumnA on MyTable(ColumnA); 

such an index will be used both for the main query and for the internal query, which will give you very good performance.

Answer 3

I usually like to avoid getting into the table more than once in the query - this will work well even without an index - doing just one scan on the table:

 SELECT columnA, columnB, columnC FROM (SELECT mytable.* ,COUNT(*) OVER (PARTITION BY columnA) countA FROM mytable) WHERE countA > 1 

Answer 4

 SELECT T.columnA, S.columnB, S.columnC FROM ( SELECT columnA FROM someTable GROUP BY columnA HAVING COUNT(*) > 1 ) T INNER JOIN someTable S ON T.columnA = S.columnA 

Answer 5

You can do this using analytic functions. Find min and max and return the lines where they are different:

 select columnA, columnB, columnC from (select t.*, min(t.columnC) over (partition by columnA, columnB) as minC, max(t.columnC) over (partition by columnA, columnB) as maxC from t ) t where minC <> maxC; 

Answer 6

This thread may be old, but it is worth updating the best / effective solution for finding duplicate records. You can use the section to search for duplicate records in the corresponding columns (as many columns as you need without using an internal join).

 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY your_key_column) AS duplicate_count FROM yourtable t ) WHERE duplicate_count > 1 --get duplicate records for the matching value in column1 and column2 

See the original answer from @Quassnoi on here . Thanks for that, a very smart solution using the section.

Answer 7

If you are looking for records with a unique identifier in your database, where several keys can be found in a column, then an easy way to find them is to create two tables, for example, the following:

Here: TICKETID is the primary key, TKTNUMBER can occur several times.

 CREATE TABLE TEMP ( TICKETID FLOAT, TKTNUMBER FLOAT ); CREATE TABLE TEMP2 ( TKTNUMBER FLOAT, COUNTER INTEGER ); 

Put all TICKETID and TKTNUMBER, looking only at TKTNUMBERS with COUNT (TKTNUMBER)> 1:

 INSERT INTO TEMP SELECT TICKETID, TKTNUMBER FROM YOUR_TABLE WHERE TKTNUMBER IN ( SELECT TKTNUMBER FROM YOUR_TABLE HAVING COUNT (TKTNUMBER) > 1 GROUP BY TKTNUMBER); 

Finally, to see the counter, enter TKTNUMBER and COUNT in the same way as above:

 INSERT INTO TEMP2 SELECT TKTNUMBER, COUNT (TKTNUMBER) AS COUNTER FROM YOUR_TABLE HAVING COUNT (TKTNUMBER) > 1 GROUP BY TKTNUMBER ORDER BY 2 DESC 

You can choose the following (by joining two tables in tktnumber):

 SELECT T1.TICKETID, T1.TKTNUMBER, T2.COUNTER FROM TEMP T1 INNER JOIN TEMP2 T2 ON T2.TKTNUMBER = T1.TKTNUMBER ORDER BY T2.COUNTER DESC