Delete duplicated data

Question

How do I delete duplicated data in the absen table below?

1. field table absen (date,time,id_user)
2. field table employee (name,department,address,id_user)

I tried the query:

select absen.date,min(absen.time),max(absen.time),absen.id_user,employee.name
from absen,employee 
where absen.id_user = employee.id_user 
group by absen.tanggal having employee.id_user = '00600320' and absen.tanggal between '2015/01/01' and '2015/01/31' 
order by absen.tanggal asc 

I want to delete all data which is not included in this query, because employees have more than one row per day.

Example :

2015/01/01 08:00:00 00600320
2015/01/01 08:00:08 00600320
2015/01/01 08:00:15 00600320
2015/01/01 17:10:00 00600320
2015/01/01 17:00:10 00600320
2015/01/01 17:00:12 00600320

I just want take up min and max from table absen and delete all between min and max.

NB : i want delete table.

Answer 1

Do it in two steps:

1.Get your data with their related min and max dates:

you can use CONCAT function to concat date and time.

select  
        t.id_user,
        min(CONCAT(t.date,' ',t.time)) mindate,
        max(CONCAT(t.date,' ',t.time)) maxdate 
from absen t
join employee on t.id_user = employee.id_user 
group by t.id_user ; 

2.Remove the data which does not relates or belongs to above wanted result set.

create another table (or temporary table), insert the none duplicate data(max and mins you wanted) into it and then remove the data from original table which is not in the newly created table using row_subquery and finally drop the newly created table, I created another table because can not use delete and select on same table at same time:

create table tbl(id_user int,
                          mindate datetime,
                          maxdate datetime);
insert into tbl
select  
        t.id_user,
        min(CONCAT(t.date,' ',t.time)) mindate,
        max(CONCAT(t.date,' ',t.time)) maxdate 
from absen t
join employee on t.id_user = employee.id_user 
group by t.id_user ;

delete from absen
where 
row(id_user,
    CONCAT(date,' ',time)) not in
(select id_user,mindate from tbl
 union
 select id_user,maxdate from tbl);
drop table tbl;

Here is THE DEMO of what I did.