Find and DELETE duplicate records by using analytical functions in sql

 
My Tech Channel link:- https://www.youtube.com/oracleshooter
 
--===Find Duplicate record by using Analitical Function ============
 
---------- Tables -------
 
select * from  XX_EMP
 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

 
 
 
 
select * from XX_EMP order by 1
 
INSERT INTO XX_EMP
select * from XX_EMP where rownum <2
 
 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20
7934	MILLER	CLERK	7782	23-Jan-82	1300		10
9001	Manoj	CLERK		08-Nov-20	1000	450	40

 
 
DESC XX_EMP
 
 
select DISTINCT * from XX_EMP
 
select  EMPNO , ENAME   , JOB    , MGR     , HIREDATE , SAL   , COMM , DEPTNO
from XX_EMP
group by EMPNO , ENAME   , JOB    , MGR     , HIREDATE , SAL   , COMM , DEPTNO
 
 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

 
 
select  rownum ,
EMPNO , ENAME   , JOB    , MGR     , HIREDATE , SAL   , COMM , DEPTNO
from XX_EMP
order by EMPNO , ENAME   , JOB    , MGR     , HIREDATE , SAL   , COMM , DEPTNO
 
 

ROWNUM	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1	7369	SMITH	CLERK	19-Aug-21	17-Dec-80	800		20
18	7369	SMITH	CLERK	19-Aug-21	17-Dec-80	800		20
15	7369	SMITH	CLERK	19-Aug-21	17-Dec-80	800		20
2	7499	ALLEN	SALESMAN	27-Jan-21	20-Feb-81	1600	300	30
16	7499	ALLEN	SALESMAN	27-Jan-21	20-Feb-81	1600	300	30
3	7521	WARD	SALESMAN	27-Jan-21	22-Feb-81	1250	500	30
17	7521	WARD	SALESMAN	27-Jan-21	22-Feb-81	1250	500	30
4	7566	JONES	MANAGER	17-Jun-21	02-Apr-81	2975		20
5	7654	MARTIN	SALESMAN	27-Jan-21	28-Sep-81	1250	1400	30
6	7698	BLAKE	MANAGER	17-Jun-21	01-May-81	2850		30
7	7782	CLARK	MANAGER	17-Jun-21	09-Jun-81	2450		10
8	7788	SCOTT	ANALYST	17-Sep-20	09-Dec-82	3000		20
9	7839	KING	PRESIDENT		17-Nov-81	5000		10
10	7844	TURNER	SALESMAN	27-Jan-21	08-Sep-81	1500	0	30
11	7876	ADAMS	CLERK	27-Apr-21	12-Jan-83	1100		20
12	7900	JAMES	CLERK	27-Jan-21	03-Dec-81	950		30
13	7902	FORD	ANALYST	17-Sep-20	03-Dec-81	3000		20
14	7934	MILLER	CLERK	21-Apr-21	23-Jan-82	1300		10
19	9001	Manoj	CLERK		08-Nov-20	1000	450	40

 
select --rownum,
EMPNO , ENAME   , JOB    , MGR     , HIREDATE , SAL   , COMM , DEPTNO,
 row_number ()
 over (Partition by EMPNO , ENAME   , JOB    , MGR     , HIREDATE , SAL   , COMM , DEPTNO order by rownum) Record_Count
from XX_EMP
 
 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	RECORD_COUNT
7369	SMITH	CLERK	7902	17-Dec-80	10-Mar-02		20	1
7369	SMITH	CLERK	7902	17-Dec-80	10-Mar-02		20	2
7369	SMITH	CLERK	7902	17-Dec-80	10-Mar-02		20	3
7499	ALLEN	SALESMAN	7698	20-Feb-81	18-May-04	300	30	1
7499	ALLEN	SALESMAN	7698	20-Feb-81	18-May-04	300	30	2
7521	WARD	SALESMAN	7698	22-Feb-81	03-Jun-03	500	30	1
7521	WARD	SALESMAN	7698	22-Feb-81	03-Jun-03	500	30	2
7566	JONES	MANAGER	7839	02-Apr-81	22-Feb-08		20	1
7654	MARTIN	SALESMAN	7698	28-Sep-81	03-Jun-03	1400	30	1
7698	BLAKE	MANAGER	7839	01-May-81	20-Oct-07		30	1
7782	CLARK	MANAGER	7839	09-Jun-81	15-Sep-06		10	1
7788	SCOTT	ANALYST	7566	09-Dec-82	18-Mar-08		20	1
7839	KING	PRESIDENT		17-Nov-81	08-Sep-13		10	1
7844	TURNER	SALESMAN	7698	08-Sep-81	08-Feb-04	0	30	1
7876	ADAMS	CLERK	7788	12-Jan-83	04-Jan-03		20	1
7900	JAMES	CLERK	7698	03-Dec-81	07-Aug-02		30	1
7902	FORD	ANALYST	7566	03-Dec-81	18-Mar-08		20	1
7934	MILLER	CLERK	7782	23-Jan-82	23-Jul-03		10	1
9001	Manoj	CLERK		08-Nov-20	26-Sep-02	450	40	1

 
 
select * from (
select --rownum,
EMPNO , ENAME   , JOB    , MGR     , HIREDATE , SAL   , COMM , DEPTNO,
 row_number ()
 over (Partition by EMPNO , ENAME   , JOB    , MGR     , HIREDATE , SAL   , COMM , DEPTNO order by rownum) Record_Count
from XX_EMP
) AA
where AA.Record_Count <> 1
 
 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	RECORD_COUNT
7369	SMITH	CLERK	7902	17-Dec-80	800		20	2
7369	SMITH	CLERK	7902	17-Dec-80	800		20	3
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30	2
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30	2