CODEWITHSUNDEEP
sqloracle-databasegroup-byhaving-clause
Jean
Jean

Reputation: 147

Oracle - group by in having clause

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.

Upvotes: 0

Views: 171

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

select f.entity if the Max(whencreated) of that entity in table f is more than the MAX((b.endtime))

Well, how about aggregating before joining:

select f.entity
from (select f.entity, max(whencreated)as maxwc
      from fan f
      group by f.entity
     ) f join
     (select b.entity, max(b.endtime) as maxet
      from brun b
      group by b.entity
     ) b
     on f.entity = b.entity and f.maxwc > b.maxet

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

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.

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48111

I think that the reason for the error is that your subquery in the HAVING clause references f.cny#. Since that column is not in the GROUP BY clause of the main query, it can't be referenced at this point.

I think you need to clarify what it is you are trying to achieve. "The same entity" implies that same value for the entity columns, and nothing else.

Upvotes: 0

Related Questions