SQL Oracle - how to find the final value for the same table?

user_11275131 0
Viewed 48

So I have a table that looks like this:

+---------+-------------+-----------+
|  Name   | Name_Change |  Status   |
+---------+-------------+-----------+
| Rick    | Brandon     | Cancelled |
| Brenda  | Alexa       | Active    |
| Brandon | TJ          | Cancelled |
| TJ      | Jonathan    | Active    |
| Randy   |             | Active    |
+---------+-------------+-----------+

So Rick --> Brandon --> TJ --> Jonathan

So my output should be:

+------+------------+--------+
| Name | Final Name | Status |
+------+------------+--------+
| Rick | Jonathan   | Active |
+------+------------+--------+

How do I code this on SQL?

TIA

oracle11g oracle sql
2 Answers

You can use a recursive CTE, as in:

with
n (name, name_change, status, version) as (
  select t.*, 1 from t where name = 'Rick'
 union all
  select n.name, t.name_change, t.status, n.version + 1
  from n
  join t on t.name = n.name_change
)
select *
from n
where version = (select max(version) from n);

See running example at SQL Fiddle.

user_64361910

This is a typical example of Hierarchical Query which starts with the member Name = 'Rick'. Filter out the records by using CONNECT_BY_ISLEAF pseudocolumn as WHERE CONNECT_BY_ISLEAF = 1 in order to have the topmost(e.g. deepest or leaf) element. Btw, it's possible to derive the whole path after starting member(Rick) by using SYS_CONNECT_BY_PATH() function, and derive the Name depending on the shortest hierarchy path returning from this function. Therefore, use

WITH t AS
(
 SELECT MAX(Name) KEEP ( DENSE_RANK FIRST 
                         ORDER BY LENGTH(SYS_CONNECT_BY_PATH(Name_Change, ' ->')) )
                         OVER () AS "Name",
        SYS_CONNECT_BY_PATH(Name_Change, ' ->') AS "Hierarchy Path",
        Name_Change AS "Final Name", 
        Status AS "Status",
        CONNECT_BY_ISLEAF AS cbi
   FROM tab t
  START WITH Name = 'Rick'
CONNECT BY PRIOR Name_Change = Name
)
SELECT "Name", "Hierarchy Path", "Final Name", "Status"
  FROM t
 WHERE cbi = 1;

Demo

user_58413060
Related