Wednesday, September 24, 2014

Duplicate values in a table


Create table sql :

 CREATE TABLE ABC (  ID NUMBER , NAME VARCHAR2(20 BYTE) ) ;

Lets insert duplicate values into it:

INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('11', 'B');
INSERT INTO ABC (ID, NAME) VALUES ('11', 'B');
INSERT INTO ABC (ID, NAME) VALUES ('13', 'G');
INSERT INTO ABC (ID, NAME) VALUES ('13', 'G');

Now table looks like this:

                  

 Now we have a duplicate combination of 2 columns.
sql to find duplicate values combination in two columns:
 
 select ID,NAME, count(ID) from ABC group by ID,NAME having count(ID) > 1;

sample output:


 


Find the duplicate ID's in a column :

 select ID, count(ID) from ABC group by ID having count(ID) > 1;


Sample output:



 Retrieving duplicate values in database :


SELECT a.*
FROM tablename a
INNER
JOIN
(
SELECT column1
, column2
FROM table1
GROUP
BY column1
, column2
HAVING Count(*) >1
) b
ON a.column1 = b.column1
AND a.column2 = b.column2



If you have combination of multiple columns that you want to check duplicates. 

For example : Check duplicates for combination of multiple columns check this link.

http://arvindasdba.blogspot.com/2016/02/check-duplicates-for-combination-of.html



No comments:

Post a Comment