Think you have a table like this:
| Employee No | Attendance Date | In Time |
| A0001 | 01-DEC-2009 | 08.00 AM |
| A0002 | 01-DEC-2009 | 08.30 AM |
| A0001 | 01-DEC-2009 | 08.20 AM |
| A0002 | 01-DEC-2009 | 08.40 AM |
| A0003 | 01-DEC-2009 | 08.40 AM |
In this table employee no A0001 and A0002 comes twice in same attendance date. Now you think that you need to remove the duplicate date from that table. But how will you do it?
The solution is rowid.
A rowid is a pseudo column (like versions_xid), that uniquely identifies a row within a table. Like:
| Rowid | Employee No | Attendance Date | In Time |
| 1 | A0001 | 01-DEC-2009 | 08.00 AM |
| 2 | A0002 | 01-DEC-2009 | 08.30 AM |
| 3 | A0001 | 01-DEC-2009 | 08.20 AM |
| 4 | A0002 | 01-DEC-2009 | 08.40 AM |
| 5 | A0003 | 01-DEC-2009 | 08.40 AM |
Now using this rowid you can easily remove the duplicate data.
See the following code:
1: Delete FROM Attendance_Details
2: WHERE rowid not in
3: (SELECT MIN(rowid) FROM attendance_details
4: GROUP BY att_date,empno);
First understand what inner query do:
1: SELECT MIN(rowid) FROM attendance_details
2: GROUP BY att_date,empno;
It will find out the single row of same employee’s same day attendance though minimum row id. Like:
|
Rowid
|
Employee No
|
Attendance Date
|
In Time
|
|
1
|
A0001
|
01-DEC-2009
|
08.00 AM
|
|
2
|
A0002
|
01-DEC-2009
|
08.30 AM
|
|
5
|
A0003
|
01-DEC-2009
|
08.40 AM
|
From above data you can easily understand that, employee no A0001 at 01-DEC-2009 minimum row no is 1. Same things happens with other employees also.
Now check the delete command.
1: delete FROM attendance_details
2: WHERE rowid not in
3: (SELECT MIN(rowid)
4: FROM attendance_details
5: GROUP BY att_date,empno) ;
Now delete command delete only those rows that will not come in inner query. That means following rows will be deleted:
|
Rowid
|
Employee No
|
Attendance Date
|
In Time
|
|
2
|
A0002
|
01-DEC-2009
|
08.30 AM
|
|
4
|
A0002
|
01-DEC-2009
|
08.40 AM
|
I will post another blog where I will give you solution for deleting the duplicate row in SQL server.