Select SQL Query - Oracle 19c

Writing SELECT SQL Query to identify following records in Oracle 19c.

Data Set

There is Account number and previous account number. As the new account number is created, the previous account number of same groups gets copied in previous number column of new row. So in above data set there are three groups, i.e. 4, 6 and 9, hence need latest row from each group.

Expected Output

Two approaches come to mind: assuming you only want the last child in the series. Since a series is based on a parent child relationship, we can just pull back all records which are not parents. Also, you did not explain how you want to find the "last" record if there are multiple children in the last leaf node. A such, I assumed, in such a case (if one exists), you want them all.

I generally find set based operations to be quite performant; so this will likely be the most efficient.

SELECT ACCOUNT_NUMBER
FROM TABLE
MINUS
SELECT PREVIOUS_NUMBER
FROM TABLE

This is an alternative method using a correlated subquery and "not exists"

SELECT * 
FROM Table A
WHERE NOT EXISTS (SELECT 1 
                  FROM Table B 
                  WHERE A.ACCOUNT_NUMBER = B.PREVIOUS_NUMBER)

Either approach above simply looks for account_numbere which are not previous_number set.. This only works because of the parent/child hierarchy; and that you're only after "Leaf" nodes.

Alternatively, We could write a recursive CTE, or a connect by prior and check the isleaf indicator; but that sems like overkill since all you want are the records which aren't parents.

Similar Q&A: HERE: using connect by prior; and or Recursive CTEs.