michalsol michalsol - 16 days ago 5
SQL Question

Oracle SQL - select rows with the highest LEVEL

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?

Answer

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