Removing Partitioned rows from oracle sql query output

Question

I have below query

SELECT ROW_NUMBER() OVER ( PARTITION BY COLUMN1,  COLUMN2, COLUMN3 ORDER BY COLUMN1, COLUMN2) AS ROW_NUM, COLUMN1, COLUMN2, COLUMN3
FROM (SUBQUERY)
GROUP BY COLUMN1, COLUMN2, COLUMN3

OUTPUT of above query:-

I need to perform something equivalent to

IF (COLUMN2 == 'PQR' AND COLUMN3 IS NOT NULL)
THEN 
"Delete whole partition from output having value A3 in column1"

Explaination:-

If COLUMN2 is having value PQR and COLUMN3 is having any DATE_TIME (i.e. NOT NULL) then all the corresponding COLUMN1 value should not be present in output of query.

OUTPUT required is:-

I tried to be as clear as I can be. Let me know if I need to clarify my question more.

NOTE:- I want to remove those rows only from output of the query not from actual table.

Answer 1

If you are doing this using a subquery, then you might want to use window functions:

SELECT s.*
FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY COLUMN1,  COLUMN2, COLUMN3 ORDER BY COLUMN1, COLUMN2) AS ROW_NUM,
             COLUMN1, COLUMN2, COLUMN3,
             COUNT(CASE WHEN COLUMN2 = 'PQR' THEN COLUMN3 END) OVER (PARTITION BY COLUMN1) as cnt
      FROM (SUBQUERY)
      GROUP BY COLUMN1, COLUMN2, COLUMN3
     ) s
WHERE cnt = 0;

This counts the number of COLUMN3 values where COLUMN2 = 'PQR' over all each COLUMN1. It then returns only the rows where this count is 0.

The advantage of this approach is that it only evaluates the subquery once -- that can be a performance win (over NOT EXISTS) if it is complicated.