[Oracle] SYS_CONNECT_BY_PATH

• SYS_CONNECT_BY_PATH is valid only in hierarchical queries.
• Provided from oracle9i.
• It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.
• Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
• The string returned is of VARCHAR2 datatype and is in the same character set as column.

* 在 Oracle 中，rownum 會比 group by、order by更先執行，因此要用子查詢。

select xxx from (select xxx from xxx order by xxx) where rownum<=xx


* Reference
- SYS_CONNECT_BY_PATH
- Rownum搭配Order by - 馬小玲傳說- 點部落
- SYS_CONNECT_BY_PATH函数用法 ORACLE - XZC.Log - BlogJava