Showing posts with label connect prior distinct. Show all posts
Showing posts with label connect prior distinct. Show all posts

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.