Thursday, February 2, 2012

Sorting in a recursive query

Here is a neat utility from Oracle to query a table recursively, and sort results among siblings:

SELECT e.id, e.name, e.manager_id
FROM employee e
START WITH ID = 1
CONNECT BY manager_ID = PRIOR ID
order siblings by e.name;

Note the 'order siblings by' clause. This can be used only with recursive queries and sorts the siblings by the name.

No comments: