How to include CONNECT_BY_PRIOR into GROUP BY clause?

user_13419845 0
Viewed 38
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?

oracle sql
1 Answers

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.

user_134198450
Related