Hi All,
I am looking for your inputs to eliminate the UNION condition and rewrite the below query in a single query.
Please provide your inputs to merge both.
SELECT c1, c2
FROM (
SELECT t1.c1, t1.c2
FROM t1 t1, d d
, (SELECT t2.c4, MAX(t2.c5) c5
FROM t2 t2
GROUP BY t2.c4) t2
WHERE t1.c3 = 'A'
AND t1.c4 = d.c4
AND d.c5 > t2.c5
UNION
SELECT t1.c1, t1.c2
FROM t1 t1, d d
WHERE t1.c3 = 'A'
AND t1.c4 = d.c4
AND NOT EXIST
(SELECT NULL
FROM t2
WHERE d.c4 = t2.c4))