MAX not needed
Donat Callens, August 11, 2010 - 4:01 am UTC
You can use this version also. It replaces the MAX trick with CONNECT_BY_ISLEAF.
SELECT deptno,
sys_connect_by_path (ename, ',')
FROM
(SELECT deptno,
ename,
ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY ename) rn
FROM emp
)
WHERE connect_by_isleaf = 1
CONNECT BY rn = PRIOR rn + 1
AND deptno = PRIOR deptno
START WITH rn = 1;
The result is the same. But I find it more intuitive to read.
Excellent point
A reader, August 16, 2010 - 6:02 pm UTC
I'm sure I thought there was a good reason for the DENSE_RANK LAST construction when I posted that in 2004, but you're quite right, it's not needed and neither is the second row_number() expression.
Both versions work fine for me in 11.1 though, and the use of deptno is unchanged, so I would be interested to hear whether the OP still gets the "invalid identifier" error. Perhaps there is a clue in the "PS".
I like connect_by_isleaf as well - thanks Donat!
connect_by_isleaf
Dennis Schnell, April 12, 2012 - 6:18 am UTC
Hi I tested also the second statement, but it doesn't work on Oracle 9i.
If you try to use the "connect_by_isleaf" in the where clause you will get the error message: ORA-00904: "CONNECT_BY_ISLEAF": invalid identifier
This works perfect on 10g, but not on 9i - just to keep it in your mind ;-)