How can I rewrite the syntax for obtaining colC so that I still only
get the record that was most recently added, but I dont have to query
tabB twice?


SELECT colA,
colB,
(select colC
from tabB
where colD = 'FOO'
and add_date = (select max(add_date)
from tabB
where colD = 'FOO')) colC
FROM tabA
where colA = 'FOO'

ORA92> select * from tabA;

COLA COLB
---------- ----------
FOO BAR
XYZ ABC

ORA92> select * from tabB;

COLC COLD ADD_DATE
---------- ---------- ------------------------
AAA FOO 10-MAY-05 15:20:52
BBB FOO 19-MAY-05 15:21:05
UUU XYZ 15-MAY-05 15:21:25
YYY XYZ 31-MAR-05 15:21:38

ORA92>
ORA92> select colA, colB, min(colC) keep (dense_rank last order by
(add_date))
2 from tabA, tabB
3 where tabA.colA = tabB.colD
4 and tabA.colA = 'FOO'
5 group by colA, colB
6 /

COLA COLB MIN(COLC)K
---------- ---------- ----------
FOO BAR BBB

ORA92>
ORA92>
ORA92> select colA, colB, min(colC) keep (dense_rank last order by
(add_date))
2 from tabA, tabB
3 where tabA.colA = tabB.colD
4 and tabA.colA = 'XYZ'
5 group by colA, colB
6 /

COLA COLB MIN(COLC)K
---------- ---------- ----------
XYZ ABC UUU