How do you write a Vertica SQL Statement to delete all entries that do not match a condition?

I need to delete all entries of an ID in a table where the day of a timestamp of an activity does not equal the day of a timestamp of the activity "item started".

This is what my table looks like:

**OrderID       TimeStamp               Activity**
1   2017-10-24 18:28:44.380     Item Started
1   2017-10-24 18:08:12.867     Task 1 complete
2   2017-10-24 18:08:47.133     Item Started
2   2017-10-25 18:09:05.267     Task 1 complete

In this case, I would need to delete all entries for OrderID 2, since the day of the activity "Task 1 complete" is the 25th and not the 24th (which was the day of the activity "item Started".

Solution

You need to pick the list in a more complex query.

What you describe above would be consistent with a further simplification of the condition:

The rows of any orderid whose last timestamp's date is not equal to it's first timestamp's date must be removed.

Creating the table:

DROP TABLE IF EXISTS input;
CREATE TABLE input(orderid,ts,activity) AS (
          SELECT 1,TIMESTAMP '2017-10-24 18:28:44.380','Item Started'
UNION ALL SELECT 1,TIMESTAMP '2017-10-24 18:08:12.867','Task 1 complete'
UNION ALL SELECT 2,TIMESTAMP '2017-10-24 18:08:47.133','Item Started'
UNION ALL SELECT 2,TIMESTAMP '2017-10-25 18:09:05.267','Task 1 complete'
)
;

So: Delete the rows of any orderid whose last timestamp's date is not equal to it's first timestamp's date:

DELETE FROM input
WHERE orderid IN (
  SELECT
    orderid
  FROM input
  GROUP BY
    orderid
  HAVING MAX(ts::DATE) <> MIN(ts::DATE)
)
;