Oracle join to get max data and a non-grouped column -

consider part of query:

select field1, field2, field3, ...  left join (     select field1, field2, max(field3) field3     table     field2 in ('1','2','3','4')     , field4 > sysdate - 365     group field1, field2) jointable on other.fk= jointable.field1 

so field4 date. need date table. if add select list must add group , such no longer grouped in way pull max(field3).

i join table again on primary keys doesn't seem ideal. there way accomplish this?

you use the aggregate keep dense_rank sytnax date associated maximum field3 value each field1/2 combination:

select field1, field2, field3, ...  left join (     select field1, field2, max(field3) field3,         max(field4) keep (dense_rank last order field3) field4     table     field2 in ('1','2','3','4')     , field4 > sysdate - 365     group field1, field2) jointable on other.fk= jointable.field1 

quick demo of subquery, cte simple data, highest field3 not on latest field4 date:

with your_table (field1, field2, field3, field4) (   select 'a', '1', 1, date '2016-11-01' dual   union select 'a', '1', 2, date '2016-09-30' dual ) select field1, field2, max(field3) field3,   max(field4) keep (dense_rank last order field3) field4 your_table field2 in ('1','2','3','4') , field4 > sysdate - 365 group field1, field2 /  f f     field3 field4     - - ---------- ---------- 1          2 2016-09-30