sql - Oracle SQL - 选择具有最高级别的行

Question

我有如下查询:

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

我得到:

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

我想要的是获得最高级别的名称。我的意思是:

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

我试过如下:

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);

但它不起作用。我得到'00979。 00000 - 不是 GROUP BY 表达式错误。为什么？我怎样才能让它发挥作用？

Answer 1

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');