Delete duplicate rows accoding to condition

Question

I am using PostgreSQL database. I have the data like below.

   id      name1              name2            modified_date
   - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   40      Balfe           | Pat            | 2010-10-01 17:48:44.804823 
   41      Battigan        | David          |                            
   42      Balfe           | Pat            | 2010-10-01 12:46:49.627593 
   44      Balfe           | Pat            | 2010-10-04 14:04:28.72415  
   45      Balfe           | Pat            | 
   46      Balfe           | Pat            | 2010-10-01 17:30:39.193153 
   47      Balfe           | Pat            |                            

I want to delete some rows from the table. The conditions to delete are:

1. It will delete duplicate records with modified_date = Null
   --> one copy of duplicate records should not be deleted.
2. Records with modified date should not be deleted although they are duplicate.

From the table above, the record with id 47 or 45 should be deleted.

Answer 1

This one should delete all records with modified_date Is Null, where another record with the same name1 and name2 exists, which also has modified_date Is Null.

The record with the lowest id will not be deleted.

Delete From your_table t1
Where modified_date Is Null
And Exists ( Select 1
             From your_table t2
             Where t2.name1 = t1.name1
               And t2.name2 = t2.name2
               And t2.modified_date Is Null
               And t2.id < t1.id
           )

Answer 2

Hmm, something like:

DELETE FROM tablename 
WHERE id NOT IN(SELECT DISTINCT id 
                FROM tablename 
                WHERE modified_date = Null)

Not sure if PostgreSQL supports subqueries with an IN statement though.