Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
AS
                 SELECT 1 AS id, 12345 AS col1,   100 AS col2,      'A' As col3
FROM dual  UNION SELECT 2, 12345,   null   ,'A'
FROM dual  UNION SELECT 3, 12345,   100       ,'B'
FROM dual  UNION SELECT 4, 12346,   0        ,'A'
FROM dual  UNION SELECT 5, 12346,  null   ,'A'
FROM dual  UNION SELECT 6, 12346,   100      ,'B'
FROM dual  UNION SELECT 7, 12346,   20       ,'B' FROM dual
7 rows affected

WITH cte AS (
  SELECT t.*, MAX(col2) OVER(PARTITION BY col1) m
  FROM t
)
SELECT *
FROM t
WHERE (COL1,COL3) IN (SELECT col1, col3 FROM cte WHERE col2 = m)


ID
COL1
COL2
COL3


3
12345
100
B

2
12345
null
A

1
12345
100
A

7
12346
20
B

6
12346
100
B


select t.*
from t
where t.col3 = (select max(t2.col3) keep (dense_rank first order by col2 desc)
                from t t2
                where t2.col1 = t.col1
               );


ID
COL1
COL2
COL3


1
12345
100
A

2
12345
null
A

4
12346
0
A

5
12346
null
A