Tuesday 17 April 2012

CONNECT BY PRIOR ORDER SIBLINGS BY not ordering correctly

Came across a situation recently where a developer was using a connect by prior order by siblings and the order was all over the place.
It was a fairly lengthy query and I spent a fair amount of time looking at it before I realised there was a DISTINCT clause at the start which was not actually needed.
The DISTINCT completely wrecks the order.

Here is a simple example
SELECT LEVEL,
 a.employee_id,
 a.first_name,
 a.last_name,
 a.manager_id
FROM employees a
START WITH a.employee_id = 100
CONNECT BY NOCYCLE PRIOR a.employee_id = a.manager_id
ORDER SIBLINGS BY a.last_name
Now if we add DISTINCT
SELECT DISTINCT
 LEVEL,
 a.employee_id,
 a.first_name,
 a.last_name,
 a.manager_id
FROM employees a
START WITH a.employee_id = 100
CONNECT BY NOCYCLE PRIOR a.employee_id = a.manager_id
ORDER SIBLINGS BY a.last_name
The connect by prior seesm to be ignored and the order is based purely on the order siblings by a.last_name

I'm not sure if this is a bug. But it is something to be aware of if you think you need to put a distinct in when using a connect by prior.

No comments:

Post a Comment