3 Ways to Check Duplicate Record in table Oracle Sql
How to check duplicate record in table SQL/Oracle?
- Create table using following script:
1CREATE TABLE TEST_EMP
2(
3 ENAME VARCHAR2(10 BYTE),
4 DEPTNO NUMBER(2),
5 ID NUMBER
6)
- Run following script for demo purpose.
1SET DEFINE OFF;
2Insert into TEST_EMP (ENAME, DEPTNO, ID)
3 Values ('SMITH', 20, 1);
4Insert into TEST_EMP (ENAME, DEPTNO, ID)
5 Values ('ALLEN', 30, 2);
6Insert into TEST_EMP (ENAME, DEPTNO, ID)
7 Values ('SMITH', 20, 3);
8Insert into TEST_EMP (ENAME, DEPTNO, ID)
9 Values ('VIKAS', 10, 4);
10Insert into TEST_EMP (ENAME, DEPTNO, ID)
11 Values ('VIKAS', 10, 6);
12Insert into TEST_EMP (ENAME, DEPTNO, ID)
13 Values ('ASHISH', 40, 7);
14Insert into TEST_EMP (ENAME, DEPTNO, ID)
15 Values ('SATISH', 50, 8);
16Insert into TEST_EMP (ENAME, DEPTNO, ID)
17 Values ('SATISH', 50, 9);
18COMMIT;
Solution 1 :
1SELECT * FROM test_emp
2WHERE ID NOT IN
3(SELECT MIN (ID) FROM test_emp GROUP BY ename);
4
5Or
6
7SELECT * FROM test_emp
8WHERE ID NOT IN
9(SELECT max (ID) FROM test_emp GROUP BY ename);
Solution 2 :
1SELECT * FROM test_emp a
2 WHERE ID > (SELECT MIN (ID)
3 FROM test_emp b
4 WHERE a.deptno = b.deptno AND a.ename = b.ename);
Solution 3 :
1SELECT * FROM test_emp
2 WHERE ID IN (
3 SELECT ID FROM
4 (SELECT ID,
5 ROW_NUMBER () OVER (PARTITION BY ename, deptno ORDER BY ID) r
6 FROM test_emp)
7 WHERE r > 1);