Remove Duplicate Numbers Status exist?

Question

How to remove the duplicate into a new table?

For example in the table:

id |   number      |    status    | statusdate 
1  | 12121312      |   Processing | 01/06/2015  12:32:00
2  | 12121312      |   Processing | 02/06/2015  13:37:00
3  | 12121312      |   Processing | 03/06/2015  14:29:00
4  | 99999999      |   Processing | 03/06/2015  15:33:00
5  | 99999999      |   Completed  | 03/06/2015  18:39:00
6  | 99999999      |   Completed  | 04/06/2015  19:39:00

1. From id 1 to 3 there are duplicate number but there is NO "Completed" status, so get the number from the oldest date

2. From id 4 to 6, there are duplicate number but "Completed" status does exist. Get the the number from the newest date

In a new table, it should be like this:

id |   number      |    status    | statusdate 
1  | 12121312      |   Processing | 01/06/2015  12:32:00
6  | 99999999      |   Completed  | 04/06/2015  19:39:00

How can that be done?

Answer 1

Here's an unoptimized version:

DELETE FROM table_name as T
WHERE ( 
        number in (SELECT number
                   FROM table_name
                   WHERE status = 'Completed')
        AND statusdate < (SELECT MAX(statusdate)
                          FROM table_name
                          GROUP BY number
                          HAVING number = T.number)
    )
    OR
    (
        number in (SELECT number
                   FROM table_name
                   WHERE status = 'Processing')
        AND statusdate > (SELECT MIN(statusdate)
                          FROM table_name
                          GROUP BY number
                          HAVING number = T.number)                   
    );

These are the records it deletes:

id  number      status      statusdate
2   12121312    Processing  February, 06 2015 13:37:00
3   12121312    Processing  March, 06 2015 14:29:00
4   99999999    Processing  March, 06 2015 15:33:00
5   99999999    Completed   March, 06 2015 18:39:00
6   99999999    Completed   April, 06 2015 19:39:00

Just replace SELECT * FROM to DELETE FROM in the Fiddle.

Answer 2

For selecting records without duplicates:

select t1.* 
from tableName t1 JOIN
     tablename t2 on t1.number=t2.number 
                 and t1.status>=t2.status 
                 and t1.statusdate>t2.statusdate
group by t1.status
order by id

Result in SQL Fiddle

For removing duplicates from the table:

delete from tableName
where id NOT IN 
    (select t1.id 
    from tableName t1 JOIN
         tablename t2 on t1.number=t2.number 
                     and t1.status>=t2.status 
                     and t1.statusdate>t2.statusdate
    group by t1.status) as Tbl