SQL> DROP TABLE CONTACTS;
Table dropped.
SQL>
SQL> CREATE TABLE CONTACTS
2 (
3 CONTACT_ID NUMBER,
4 FIRST_NAME VARCHAR (10),
5 LAST_NAME VARCHAR (20),
6 EMAIL VARCHAR (30)
7 );
Table created.
SQL>
SQL> INSERT INTO CONTACTS VALUES(1,'Syed','Abbas','syed.abbas@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(2,'Catherine','Abel','catherine.abel@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(3,'Kim','Abercrombie','kim.abercrombie@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(4,'Kim','Abercrombie','kim.abercrombie@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(5,'Kim','Abercrombie','kim.abercrombie@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(6,'Hazem','Abolrous','hazem.abolrous@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(7,'Hazem','Abolrous','hazem.abolrous@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(8,'Humberto','Acevedo','humberto.acevedo@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(9,'Humberto','Acevedo','humberto.acevedo@example.com');
1 row created.
SQL> INSERT INTO CONTACTS VALUES(10,'Pilar','Ackerman','pilar.ackerman@example.com');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT * FROM CONTACTS;
CONTACT_ID FIRST_NAME LAST_NAME EMAIL
---------- ---------- -------------------- ------------------------------
1 Syed Abbas syed.abbas@example.com
2 Catherine Abel catherine.abel@example.com
3 Kim Abercrombie kim.abercrombie@example.com
4 Kim Abercrombie kim.abercrombie@example.com
5 Kim Abercrombie kim.abercrombie@example.com
6 Hazem Abolrous hazem.abolrous@example.com
7 Hazem Abolrous hazem.abolrous@example.com
8 Humberto Acevedo humberto.acevedo@example.com
9 Humberto Acevedo humberto.acevedo@example.com
10 Pilar Ackerman pilar.ackerman@example.com
10 rows selected.
SQL>
*******************************************************************************
SQL> DELETE FROM CONTACTS WHERE CONTACT_ID NOT IN (SELECT MIN(CONTACT_ID) FROM CONTACTS GROUP BY FIRST_NAME,LAST_NAME,EMAIL);
4 rows deleted.
SQL> SELECT * FROM CONTACTS;
CONTACT_ID FIRST_NAME LAST_NAME EMAIL
---------- ---------- -------------------- ------------------------------
1 Syed Abbas syed.abbas@example.com
2 Catherine Abel catherine.abel@example.com
3 Kim Abercrombie kim.abercrombie@example.com
6 Hazem Abolrous hazem.abolrous@example.com
8 Humberto Acevedo humberto.acevedo@example.com
10 Pilar Ackerman pilar.ackerman@example.com
6 rows selected.
*******************************************************************************
SQL> DELETE FROM CONTACTS WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM CONTACTS GROUP BY FIRST_NAME,LAST_NAME,EMAIL);
4 rows deleted.
SQL> SELECT * FROM CONTACTS;
CONTACT_ID FIRST_NAME LAST_NAME EMAIL
---------- ---------- -------------------- ------------------------------
1 Syed Abbas syed.abbas@example.com
2 Catherine Abel catherine.abel@example.com
3 Kim Abercrombie kim.abercrombie@example.com
6 Hazem Abolrous hazem.abolrous@example.com
8 Humberto Acevedo humberto.acevedo@example.com
10 Pilar Ackerman pilar.ackerman@example.com
6 rows selected.
SQL>
*****************************************************************************
SQL> DELETE FROM CONTACTS A WHERE ROWID > (SELECT MIN(ROWID) FROM CONTACTS B WHERE A.FIRST_NAME=B.FIRST_NAME AND A.LAST_NAME=B.LAST_NAME AND A.EMAIL=B.EMAIL);
4 rows deleted.
SQL> SELECT * FROM CONTACTS;
CONTACT_ID FIRST_NAME LAST_NAME EMAIL
---------- ---------- -------------------- ------------------------------
1 Syed Abbas syed.abbas@example.com
2 Catherine Abel catherine.abel@example.com
3 Kim Abercrombie kim.abercrombie@example.com
6 Hazem Abolrous hazem.abolrous@example.com
8 Humberto Acevedo humberto.acevedo@example.com
10 Pilar Ackerman pilar.ackerman@example.com
6 rows selected.
*********************************************************************************
SQL> DELETE FROM CONTACTS
2 WHERE ROWID NOT IN (SELECT "ROWID"
3 FROM (SELECT CONTACT_ID,
4 FIRST_NAME,
5 LAST_NAME,
6 EMAIL,
7 ROW_NUMBER ()
8 OVER (
9 PARTITION BY FIRST_NAME,
10 LAST_NAME,
11 EMAIL
12 ORDER BY
13 FIRST_NAME, LAST_NAME, EMAIL)
14 ROW_NUM,
15 ROWID AS "ROWID"
16 FROM CONTACTS)
17 WHERE ROW_NUM = 1);
4 rows deleted.
SQL>
SQL> SELECT * FROM CONTACTS;
CONTACT_ID FIRST_NAME LAST_NAME EMAIL
---------- ---------- -------------------- ------------------------------
1 Syed Abbas syed.abbas@example.com
2 Catherine Abel catherine.abel@example.com
3 Kim Abercrombie kim.abercrombie@example.com
6 Hazem Abolrous hazem.abolrous@example.com
8 Humberto Acevedo humberto.acevedo@example.com
10 Pilar Ackerman pilar.ackerman@example.com
6 rows selected.
No comments:
Post a Comment