Oracle SQL - How to omit all returned records if a certain criteria is met-CodePudding

I am trying to figure this situation out but for the life of me I don't know how to go about it.

Data in Table1 -

Activity_Date               Code       ID  APDC_Date
29/04/21 08:03:38 AM          2         1   29/04/21
30/04/21 03:55:37 PM          12        1   29/04/21
29/04/21 08:03:38 AM          12        2   29/04/21
30/04/21 03:55:37 PM          2         2   29/04/21
29/04/21 08:03:38 AM          11        3   29/04/21
30/04/21 03:55:37 PM          12        3   29/04/21

Condition If the Max Activity Date Record for that id has a code <> 2 and has another line item for that ID where a code is 2 , display all records for that ID except the line item that has a code is 2 If the Max Activity Date Record has a code = 2 then nothing is displayed Otherwise display all records

What I want to be returned if I use the above table data, select code,id

My current sql statement is pretty simple, I have the current statement in an AS (I have other tables I am accessing/joining)

Sara As (
 SELECT Code, ID 
     FROM table1 a
     WHERE a.apdc_date = (SELECT MAX(aa.apdc_date)
                          FROM table1 aa
                          WHERE aa.id = a.id
                          ))

and then combining those results using a join

Left JOIN sara j
     ON b.table2_id = j.id

I know there is an easy way or doing this but at this point in time I can't think of a way.

Any help would be appreciated.

CodePudding user response：

It's not totally clear exactly what you're after as your desired results are ambiguous, ideally include all the columns so it's clear which rows you're expecting. There's also no clear ordering of rows, but I'm sure that's only because it's contrived data.

I think the following gives your desired results, first using some window functions to collate data per id and then filtering accordingly. If I haven't got it quite right hopefully it's only minor.

select code, Id
from (
    select code, id, 
    First_Value(code) over(partition by id order by Activity_Date desc) as MaxCode,
    Max(case when code=2 then 1 else 0 end) over(partition by id) as Has2
    from t
)
where MaxCode != 2 and (has2 = 0 or (has2 = 1 and code != 2));

CodePudding user response：

You can use:

SELECT code,
       id
FROM   (
  SELECT code,
         id,
         -- Count the number of code=2 rows that have the latest activity date
         -- for each id.
         COUNT(CASE code WHEN 2 THEN 1 END)
           KEEP (DENSE_RANK LAST ORDER BY activity_date) OVER (PARTITION BY id)
           AS code2_latest,
         -- Count the number of code=2 rows for each id.
         COUNT(CASE code WHEN 2 THEN 1 END) OVER (PARTITION BY id)
           AS code2_count
  FROM   table_name
)
WHERE  code != 2
AND    (  code2_count  = 0
       OR code2_latest = 0);

Which, for the sample data:

CREATE TABLE table_name (Activity_Date, Code, ID, APDC_Date) AS
SELECT DATE '2021-04-29'   INTERVAL '08:03:38' HOUR TO SECOND,  2, 1, DATE '2021-04-29' FROM DUAL UNION ALL
SELECT DATE '2021-04-30'   INTERVAL '15:55:37' HOUR TO SECOND, 12, 1, DATE '2021-04-29' FROM DUAL UNION ALL
SELECT DATE '2021-04-29'   INTERVAL '08:03:38' HOUR TO SECOND, 12, 2, DATE '2021-04-29' FROM DUAL UNION ALL
SELECT DATE '2021-04-30'   INTERVAL '15:55:37' HOUR TO SECOND,  2, 2, DATE '2021-04-29' FROM DUAL UNION ALL
SELECT DATE '2021-04-29'   INTERVAL '08:03:38' HOUR TO SECOND, 11, 3, DATE '2021-04-29' FROM DUAL UNION ALL
SELECT DATE '2021-04-30'   INTERVAL '15:55:37' HOUR TO SECOND, 12, 3, DATE '2021-04-29' FROM DUAL UNION ALL
SELECT DATE '2021-04-29'   INTERVAL '08:03:38' HOUR TO SECOND, 11, 4, DATE '2021-04-29' FROM DUAL UNION ALL
SELECT DATE '2021-04-30'   INTERVAL '15:55:37' HOUR TO SECOND, 12, 4, DATE '2021-04-29' FROM DUAL UNION ALL
SELECT DATE '2021-04-30'   INTERVAL '15:55:37' HOUR TO SECOND,  2, 4, DATE '2021-04-29' FROM DUAL;

Outputs:

CODE ID

12 1

11 3

12 3

CODE	ID
12	1
11	3
12	3

db<>fiddle here