My query looks like this
select f.entity, MAX(to_char(f.whencreated, 'MM/DD/YYYY HH24:MI:SS')) from fan f
group by f.entity
having MAX((f.whencreated)) >
(select MAX((b.endtime)) from brun b
where b.cny# = f.cny#
and b.entity = f.entity
group by b.entity, f.entity);
I get error
ORA-00979: not a GROUP BY expression
In this query, I want to select f.entity if the Max(whencreated) of that entity in table f is more than the MAX((b.endtime)) of the same entity in table brun.
the tables look like as follows:
Table fan:
ENTITY WHENCREATED
A 09/01/2020 12:34:00
A 10/01/2020 12:12:12
B 08/01/2020 12:34:00
B 10/01/2020 12:12:12
Table burn:
ENTITY ENDTIME
A 09/02/2020 12:34:00
A 09/04/2020 12:12:12
B 08/01/2020 12:34:00
B 11/01/2020 12:12:12
The query should return
A 10/01/2020 12:12:12
because the max(brun.endtime) for the Entity A is 09/04/2020 12:12:12, which is less than the max(fan.whencreated) for the Entity A, which is 10/01/2020 12:12:12.
I'd try with a different approach:
with temp as
(select
f.entity,
max(f.whencreated) max_fwhen,
max(b.endtime) max_bend
from fan f join brun b on b.dny# = f.cny#
and b.entity = f.entity
group by f.entity
)
select entity
from temp
where max_fwhen > max_bend;
By the way, don't MAX a string; I believe you want to work with dates, not strings. You'll get unexpected results as e.g. 08/25/1920 is "larger" than 02/12/2021.