oracle group by id and find the rows not having value

Question

Table

Rel_ID SITE_ID   Ack   Added_date
ABC    123        Y    08/09/2023
ABC    123     (null)  08/06/2023
ABC    124     (null)  08/07/2023   
ABC    124     (null)  08/06/2023
ABC    124       N     08/05/2023
ABC    125       Y     07/06/2023
ABC    125       Y     07/07/2023
ABC    126      (null)  09/08/2023
ABC    126      (null)  09/09/2023
ABC    127      (null)  05/08/2023
ABC    127       N      05/09/2023

If a site_id is acknowledged (ack='Y') then it should not be listed otherwise if ack is null or ack is 'N' then it should be listed for Rel_id based on the added_date ascending

Resultset

Rel_ID SITE_ID   Ack   Added_date
ABC    124       N     08/05/2023
ABC    126      (null)  09/08/2023
ABC    127      (null)  05/08/2023

What i tried is below

SELECT * FROM (SELECT RR.*, RANK() OVER (PARTITION BY RR.Rel_ID ,RR.SITE_ID 
ORDER BY (CASE WHEN decode(ack, null, 'N', ack) = 'Y' then 1 else 2 end) asc, 
RR.CREATE_DTE asc) AS RANK FROM  Address_data  RR WHERE (ack is null or ack= 'N')) RS 
WHERE RANK = 1

Answer 1

You can use ROW_NUMBER, rather than RANK, and can add a filter to the outer query:

SELECT *
FROM   (
  SELECT a.*,
         ROW_NUMBER() OVER (
           PARTITION BY rel_id, site_id
           ORDER BY CASE WHEN ack = 'Y' THEN 1 ELSE 2 END, added_date
         ) AS rn
  FROM   address_data a
)
WHERE  rn = 1
AND    (ack IS NULL OR ack != 'Y');

Which, for the sample data:

CREATE TABLE address_data (Rel_ID, SITE_ID, Ack, Added_date) AS
SELECT 'ABC', 123, 'Y',  DATE '2023-08-09' FROM DUAL UNION ALL
SELECT 'ABC', 123, NULL, DATE '2023-08-06' FROM DUAL UNION ALL
SELECT 'ABC', 124, NULL, DATE '2023-08-07' FROM DUAL UNION ALL   
SELECT 'ABC', 124, NULL, DATE '2023-08-06' FROM DUAL UNION ALL
SELECT 'ABC', 124, 'N',  DATE '2023-08-05' FROM DUAL UNION ALL
SELECT 'ABC', 125, 'Y',  DATE '2023-07-06' FROM DUAL UNION ALL
SELECT 'ABC', 125, 'Y',  DATE '2023-07-07' FROM DUAL UNION ALL
SELECT 'ABC', 126, NULL, DATE '2023-09-08' FROM DUAL UNION ALL
SELECT 'ABC', 126, NULL, DATE '2023-09-09' FROM DUAL UNION ALL
SELECT 'ABC', 127, NULL, DATE '2023-05-08' FROM DUAL UNION ALL
SELECT 'ABC', 127, 'N',  DATE '2023-05-09' FROM DUAL;

Outputs:

REL_ID	SITE_ID	ACK	ADDED_DATE	RN
ABC	124	N	2023-08-05 00:00:00	1
ABC	126	null	2023-09-08 00:00:00	1
ABC	127	null	2023-05-08 00:00:00	1

fiddle