1

I have query as follows:

SELECT CONNECT_BY_ROOT(name), LEVEL
  FROM Workers
  CONNECT BY PRIOR boss=nick
  START WITH function IN ('programmer', 'designer');

I get:

BELLA   1
BELLA   2
BELLA   3
MICKEY  1
MICKEY  2
BOB     1
BOB     2
DUDE    1
DUDE    2
DUDE    3
SONIA   1
SONIA   2
SONIA   3
KATE    1
KATE    2
KATE    3
LUKE    1
LUKE    2
LUKE    3
LUKE    4

What I would like is to get names with the highest LEVEL. I mean:

BELLA   3
MICKEY  2
BOB     2
DUDE    3
SONIA   3
KATE    3
LUKE    4

I tried do it as follows:

SELECT CONNECT_BY_ROOT(name), MAX(LEVEL)
  FROM Workers
  CONNECT BY PRIOR boss=nick
  START WITH function IN ('programmer', 'designer')
  GROUP BY CONNECT_BY_ROOT(name);

but it doesn't work. I get '00979. 00000 - not a GROUP BY expression' error. Why? How can I make it works?

michalsol
  • 752
  • 8
  • 29

2 Answers2

2

One method uses a cte:

with t(name, lev) as (
      SELECT CONNECT_BY_ROOT(name), LEVEL
      FROM Workers
      CONNECT BY PRIOR boss=nick
      START WITH function IN ('programmer', 'designer')
     )
select name, max(lev)
from t
group by name;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

connect_by_isleaf

SELECT CONNECT_BY_ROOT(name), LEVEL
FROM Workers
WHERE connect_by_isleaf = 1
CONNECT BY PRIOR boss=nick
START WITH function IN ('programmer', 'designer');
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88