SQL> CREATE TABLE TEST(ID NUMBER(2), NAME VARCHAR2(50));
Table created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (10, 'HJ');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (10, 'HJ');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (20, 'KING');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (30, 'MORGAN');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (20, 'KING');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (30, 'MORGAN');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TEST;
ID NAME
---------- --------------------
10 HJ
10 HJ
20 KING
30 MORGAN
20 KING
30 MORGAN
6 rows selected.
SQL> DELETE FROM TEST
2 WHERE ROWID NOT IN (SELECT MIN (ROWID)
3 FROM TEST
4 GROUP BY ID,NAME);
3 rows deleted.
SQL> SELECT * FROM TEST;
ID NAME
---------- --------------------
10 HJ
20 KING
30 MORGAN
SQL> COMMIT;
Commit complete.
Table created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (10, 'HJ');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (10, 'HJ');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (20, 'KING');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (30, 'MORGAN');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (20, 'KING');
1 row created.
SQL> Insert into TEST
2 (ID, NAME)
3 Values
4 (30, 'MORGAN');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TEST;
ID NAME
---------- --------------------
10 HJ
10 HJ
20 KING
30 MORGAN
20 KING
30 MORGAN
6 rows selected.
SQL> DELETE FROM TEST
2 WHERE ROWID NOT IN (SELECT MIN (ROWID)
3 FROM TEST
4 GROUP BY ID,NAME);
3 rows deleted.
SQL> SELECT * FROM TEST;
ID NAME
---------- --------------------
10 HJ
20 KING
30 MORGAN
SQL> COMMIT;
Commit complete.
2 comments:
Very nice and simple approach
Good one.
Post a Comment