How to include CONNECT_BY_PRIOR into GROUP BY clause?
select r.*,max(connect_by_root(c_name)) as root , max(level)
from relat r
where connect_by_isleaf = 1
start with chld = 'srv'
connect by prior p_name = c_name
group by r.par, r.chld, r.c_name, r.p_name;
I want to include CONNECT_BY_ROOT(c_name)
into GROUP BY
clause instead of using MAX()
function on it. If I add CONNECT_BY_ROOT(c_name)
into GROUP BY
clause it throws me error not a group by expression
. So is there any way to do it?
I think the problem is that oracle takes CONNECT_BY_ROOT(c_name)
in SELECT
clause and GROUP BY
clause as different columns. And so when I try group by
it and select
oracle thinks that I'm grouping by one CONNECT_BY_ROOT(c_name)
column and trying to select another one CONNECT_BY_ROOT(c_name)
column and throws me error not a group by expression
.
So I came up with this solution:
select par, chld, c_name, p_name, root, max(lvl) as lvl
from (select r.*,connect_by_root(c_name) as root, level as lvl
from relat r
where connect_by_isleaf = 1
start with chld = 'srv'
connect by prior p_name = c_name)
group by par, chld, c_name, p_name, root;
I'm doing connect by query first and then group it by the columns I need.