Skip to content

Instantly share code, notes, and snippets.

@niisar
Created May 19, 2014 21:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save niisar/d2aa6c8f45425f30c40f to your computer and use it in GitHub Desktop.
Save niisar/d2aa6c8f45425f30c40f to your computer and use it in GitHub Desktop.
spin thru the company table sorting breath first also in depth first.
WITH RSFC(CK,PK,LVL,HIER) AS
(SELECT EMPLOYEE_ID,
MANAGER_ID,
0 AS LVL,
EMPLOYEE_NAME AS HIER
FROM COMPANY
WHERE MANAGER_ID IS NULL
UNION ALL
SELECT EMPLOYEE_ID,
MANAGER_ID,
LVL+1,
HIER
|| '/'
|| EMPLOYEE_NAME
FROM RSFC R
INNER JOIN COMPANY F
ON R.CK = F.MANAGER_ID
) SEARCH BREADTH FIRST BY CK
SET ORDR
--SEARCH DEPTH first by CK set ORDR
SELECT a.LVL,
a.CK,
a.PK,
a.HIER,
ORDR
FROM RSFC a
ORDER BY ORDR
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment