PRIOR in SELECT list

Question

I can't understand what it adds to the result of the query. From the book that I'm learning:

If you prefix a column name with PRIOR in the select list (SELECT PRIOR EMPLOYEE_ID, ...), you specify the “prior” row’s value.

SELECT PRIOR EMPLOYEE_ID, MANAGER_ID, LPAD(' ', LEVEL * 2) || EMPLOYEES.JOB_ID
  FROM EMPLOYEES
  START WITH EMPLOYEE_ID = 100
  CONNECT BY PRIOR EMPLOYEE_ID =  MANAGER_ID;

The only difference I see, is that it adds a NULL value in the first row and increments IDs of employees by 1.

score 8 · Accepted Answer · answered Mar 06 '16 at 12:16

PRIOR just takes a record from a previous record in the traversed hierarchy.
I think the best way to undestand how it works is to play with a simple hierarchy:

create table qwerty(
   id int,
   name varchar2(100),
   parent_id int
);

insert all
into qwerty values( 1, 'Grandfather', null )
into qwerty values( 2, 'Father', 1 )
into qwerty values( 3, 'Son', 2 )
into qwerty values( 4, 'Grandson', 3 )
select 1234 from dual;

The below query traverses the above hierarchy:

select level, t.*
from qwerty t
start with name = 'Grandfather'
connect by prior id = parent_id

     LEVEL         ID NAME                  PARENT_ID
---------- ---------- -------------------- ----------
         1          1 Grandfather                     
         2          2 Father                        1 
         3          3 Son                           2 
         4          4 Grandson                      3

If we add "PRIOR name" to the above query, then the name of "parent" is displayed. This vaue is taken from prevoius record in the hierarchy (from LEVEL-1)

select level, prior name as parent_name, t.*
from qwerty t
start with name = 'Grandfather'
connect by prior id = parent_id;

     LEVEL PARENT_NAME                  ID NAME                  PARENT_ID
---------- -------------------- ---------- -------------------- ----------
         1                               1 Grandfather                     
         2 Grandfather                   2 Father                        1 
         3 Father                        3 Son                           2 
         4 Son                           4 Grandson                      3

Husqvik · Answer 2 · 2016-03-06T11:54:13.750

PRIOR operator returns previous value in a hierarchy build using CONNECT BY clause.

WITH hierarchy(id, parent_id, value) AS (
    SELECT 1, NULL,          'root' FROM dual UNION ALL
    SELECT 2,    1,       'child 1' FROM dual UNION ALL
    SELECT 3,    1,       'child 2' FROM dual UNION ALL
    SELECT 4,    3, 'grand child 1' FROM dual
)
SELECT
    hierarchy.*, LEVEL depth, PRIOR value
FROM
    hierarchy
START WITH
    parent_id IS NULL
CONNECT BY
    PRIOR id = parent_id

This simple query connects the rows from root to leafs. The PRIORVALUE column returns value of VALUE column of row's parent row (predecessor within the hierarchy), so 'grand child 1' parent is 'child 2' or 'child 1' parent is 'root'. 'root', the first row within the hierarchy (LEVEL = 1) doesn't have any parent therefore PRIOR returns NULL.

If you connect the hierarchy in opposite direction, from a leaf to the root, the PRIOR operator will return child row that was used to connect the row you're looking at.

The LEVEL column shows the depth of specific row within the hierarchy.

PRIOR in SELECT list

2 Answers2