I have a fairly complex requirement I would like to solve using SQL in a Postgres DB. I'm sure this would be addressed in any order management system however I cannot find anything of a similar nature.

I have the following table (and values):

```
CREATE TABLE TABLE1 (
    ID varchar(8), 
    ORIG_ID varchar(8), 
    STATUS varchar(8), 
    VALIDITY varchar(8)
);

INSERT INTO TABLE1
    (ID, ORIG_ID, STATUS, VALIDITY)
VALUES
    ('1', '1', 'REPLACED','DAY'), 
    ('2', '1', 'REPLACED','DAY'),
    ('3', '1', 'FILLED','DAY'),
    ('4', '4', 'REJECTED','DAY'),
    ('5', '5', 'PARTIAL','GTC'),
    ('6', '6', 'EXPIRED','GTD'),
    ('7', '7', 'REPLACED','GTD'),
    ('8', '7', 'PARTIAL','GTD'),
    ('9', '9', 'FILLED', 'GTD'),
    ('10', '10', 'NEW', 'DAY'),
    ('11', '11', 'NEW', 'GTD'),
    ('12', '12', 'DFD', 'GTD'),
    ('13', '13', 'REPLACED', 'GTD'),
    ('14', '13', 'FILLED', 'GTD')
;

```

N.B - 

* Please ignore the data types on the fields
* The final table may have thousands of entries to process
* The above can be pasted directly into SQL Fiddle if required (PostgreSQL 9.3.1)

The requirements I have are:

Delete all entries that have a STATUS of either:

```
FILLED, EXPIRED, REJECTED, CANCELLED
PARTIAL/NEW - If the VALIDITY is not GTD/GTC (i.e. only DAY)
REPLACED - Unless there are other entries with the same ORIG_ID in a PARTIAL/NEW STATUS and not GTD/GTC (still working orders)

TBD - To Be Deleted:
TBD ('1', '1', 'REPLACED','DAY'), 
TBD ('2', '1', 'REPLACED','DAY'),
TBD ('3', '1', 'FILLED','DAY'),
TBD ('4', '4', 'REJECTED','DAY'),
    ('5', '5', 'PARTIAL','GTC'),
TBD ('6', '6', 'EXPIRED','GTD'),
    ('7', '7', 'REPLACED','GTD'),
    ('8', '7', 'PARTIAL','GTD'),
TBD ('9', '9', 'FILLED', 'GTD'),
TBD ('10', '10', 'NEW', 'DAY'),
    ('11', '11', 'NEW', 'GTD'),
    ('12', '12', 'DFD', 'GTD'),
TBD ('13', '13', 'REPLACED', 'GTD'),
TBD ('14', '13', 'FILLED', 'GTD')

```

I've tried looking and the closet I could find was the following:

[Delete with join on the same table and limit clause](https://stackoverflow.com/questions/18651992/delete-with-join-on-the-same-table-and-limit-clause)

However I couldn't get it to work while incorporating the requirements above.

As this will be run at the end of day I have had a few thoughts in such as changing all entries with VALIDITY of DAY, setting STATUS to EXPIRED. Then just deleting them all but then still hit the issue of STATUS with the GTD/GTC orders. I'm unsure if this would also be faster than handling it all under the same logic. 

Any help (or new ideas) would be appreciated on how to tackle this issue.