Need SQL query for below scenario

Question

I need to come up with an SQL query which will retrieve the maximum "seq" column from table1 if the corresponding "seq" column has no "C" in "Res" column in table2

Scenario:1

Table 1
+----+---------+
| Seq | Req    | 
+----+---------+
| 001 | X1     |
| 002 | X1     |
| 003 | X1     |
+----+---------+

Table2
+----+---------+--------+
|Seq | Req     | Res    | 
+----+---------+--------+
| 001| X1     |   D     | 
| 002| X1     |   D     |
| 003| X1     |   C     |
+----+---------+--------+

output : As the maximum seq (003) has 'C' in Res , I dont want to display any records here

Scenario:2

Table 1
+----+---------+
| Seq | Req    | 
+----+---------+
| 001 | X1     |
| 002 | X1     |
| 003 | X1     |
| 004 | X1     |
+----+---------+

Table2
+----+---------+--------+
|Seq | Req     | Res    | 
+----+---------+--------+
| 001| X1     |   D     | 
| 002| X1     |   D     |
| 003| X1     |   C     |
+----+---------+--------+

output : I want to display record "004,X1" from Table-1 as there is no "C" in Res column for maximum seq(004) in Table2 ( No record exists in Table2 for max seq - 004

Scenario:3

Table 1
+----+---------+
| Seq | Req    | 
+----+---------+
| 001 | X1     |
| 002 | X1     |
| 003 | X1     |
| 004 | X1     |
+----+---------+

Table2
+----+---------+--------+
|Seq | Req     | Res    | 
+----+---------+--------+
| 001| X1     |   D     | 
| 002| X1     |   D     |
| 003| X1     |   C     |
| 004| X1     |   K     |
+----+---------+--------+

output : I want to display record "004,X1" from Table-1 as there is no "C" in Res column for maximum seq(004) in Table2 .Max Seq Number in Table2 (004) is K

Tried with inner join and left join , but one or the other scenario fails. Please help me with this

Answer 1

Oracle Setup:

CREATE TABLE Table1 (SEQ, REQ) AS
  SELECT '001', 'X1' FROM DUAL UNION ALL
  SELECT '002', 'X1' FROM DUAL UNION ALL
  SELECT '003', 'X1' FROM DUAL UNION ALL
  SELECT '004', 'X1' FROM DUAL UNION ALL

  SELECT '001', 'X2' FROM DUAL UNION ALL
  SELECT '002', 'X2' FROM DUAL UNION ALL
  SELECT '003', 'X2' FROM DUAL UNION ALL

  SELECT '001', 'X3' FROM DUAL UNION ALL
  SELECT '002', 'X3' FROM DUAL UNION ALL
  SELECT '003', 'X3' FROM DUAL UNION ALL
  SELECT '004', 'X3' FROM DUAL UNION ALL

  SELECT '001', 'X4' FROM DUAL UNION ALL
  SELECT '002', 'X4' FROM DUAL UNION ALL
  SELECT '003', 'X4' FROM DUAL UNION ALL
  SELECT '004', 'X4' FROM DUAL;

CREATE TABLE Table2 (SEQ, REQ, RES) AS
  SELECT '001', 'X1', 'D' FROM DUAL UNION ALL
  SELECT '002', 'X1', 'D' FROM DUAL UNION ALL
  SELECT '003', 'X1', 'K' FROM DUAL UNION ALL
  SELECT '004', 'X1', 'C' FROM DUAL UNION ALL

  SELECT '001', 'X2', 'D' FROM DUAL UNION ALL
  SELECT '002', 'X2', 'D' FROM DUAL UNION ALL
  SELECT '003', 'X2', 'C' FROM DUAL UNION ALL

  SELECT '001', 'X3', 'D' FROM DUAL UNION ALL
  SELECT '002', 'X3', 'D' FROM DUAL UNION ALL
  SELECT '003', 'X3', 'C' FROM DUAL UNION ALL
  SELECT '004', 'X3', 'K' FROM DUAL UNION ALL

  SELECT '001', 'X4', 'D' FROM DUAL UNION ALL
  SELECT '002', 'X4', 'D' FROM DUAL UNION ALL
  SELECT '003', 'X4', 'C' FROM DUAL;

Query:

SELECT SEQ, REQ
FROM   (
  SELECT t1.*,
         MAX( t1.SEQ ) OVER ( PARTITION BY t1.REQ ) AS max_seq,
         MAX( t2.RES ) KEEP ( DENSE_RANK LAST ORDER BY t1.SEQ )
                       OVER ( PARTITION BY t1.REQ ) AS max_res
  FROM   table1 t1
         LEFT OUTER JOIN table2 t2
         ON ( t1.seq = t2.seq AND t1.req = t2.req )
)
WHERE  SEQ = max_seq
AND    max_res = 'C'

Output:

SEQ | REQ
:-- | :--
004 | X1 
003 | X2 

db<>fiddle here

Answer 2

try lik below

with cte as
(
 select *,row_number() over(order by seq desc) rn
 from table1
)    
select cte.* 
from cte left join table2 t2 on cte.seq=t2.seq
where t2.res<>'C' and cte.rn=1

Answer 3

This may be slightly different in Oracle SQL

SELECT seq, req 
    FROM table1 
    WHERE seq in (SELECT max(seq) FROM table1) 
        AND seq NOT IN (SELECT seq FROM table2 WHERE res = 'C')