Oracle SQL, return unique (max) row in subquery in SELECT header (before FROM, WHERE) -

i running sql through business objects on oracle server. have following code (simplified), runs:

select   a.col1,   a.col2,   a.date1,     (     select       b.date2           tbl b           b.id = a.id       ,       b.date2 >= a.date1-60     ) subdate   tbl   a.col1 = 'foo'   ,   a.col2 = 'bar'   ,   a.date1 = '#somedate#' 

so see, there subquery in select clause, want return single value: b.date2. work... of time. however, if b.id returns more one record date2 within last 60 days of date1, query fails.

what want limit subquery's output single recent value of date2. have tried sorts of things: max, limit 1, distinct... sql not compile, telling me have errors. example following top 1 produces error saying "from keyword not found expected":

    (     select       top 1 b.date2           tbl b           b.id = a.id       ,       b.date2 >= a.date1-60     ) subdate 

the following order by/limit produces error saying "missing right parenthesis":

    (     select       b.date2           tbl b           b.id = a.id       ,       b.date2 >= a.date1-60       order b.date2 limit 1     ) subdate 

(reproduced subqueries above).

from researching these errors, see various advice subqueries after main clause; nothing subqueries in way want use them.

can me understand why getting errors, , correct way should do want?

thanks.

oracle can pain in case (unless using oracle 12). 1 method uses keep. however, in case max() sufficient:

select . . .       (select max(b.date2)       tbl b       b.id = a.id ,             b.date2 >= a.date1 - 60      ) subdate . . . 

note: don't need condition on date2 in subquery, unless want limit results last 60 days.