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