get All Parents with no children or if there are children, get the latest child

Question

I have below table orders . orderId is the primary key and parentOrderId is the column that represents if this order has parent order .

ex: 1,2,3,4 do not have parent orders. 5,6,7,8 have parent orders.

+--------------------+----------------------+
|          order_id   | parent_order_id     |
+--------------------+----------------------+
|                  1 |                  null|
|                  2 |                  null|
|                  3 |                  null|
|                  4 |                  null|
|                  5 |                    1 |
|                  6 |                    2 |
|                  7 |                    3 |
|                  8 |                    3 |
+--------------------+----------------------+



I need to query all Parents with no children or if there are children only get the latest child. 
The result I need is : 4,5,6,8
   4 because it has no children and should be returned.
   5 because it is the only child of 1.
   6 because that is the only child of 2.
   8 because 3 has 2 children(7,8) and I need to return latest child. Pick max of orderId's.

What I tried :

SELECT
  MAX(order_id)
FROM
  orders
WHERE
  parent_order_id IS NOT NULL
GROUP BY
  parent_order_id
UNION ALL
SELECT
  order_id
FROM
  orders
WHERE
  parent_order_id IS NULL
MINUS
SELECT
  parent_order_id
FROM
  orders;

What I am looking for :

The above query returns 4,5,6,8 . The problem is I am feeding this query into IN clause and oracle has 1000 limit for IN clause. i am trying to see if there is a better way to approach this problem using joins. update : Joins will help me retrieve all the columns in order table instead of just id's , for the sake of simplicity i just included two columns, there are more in the table. right now i am fetching id's first and feeding them in clause in another query to get all columns that match those id's.

I am also looking for sql that is not vendor specific.

Thank you for your help.

Answer 1

The OP explained in a comment to my other Answer that he needs a query that uses standard SQL features as much as possible. This rules out connect by queries, and also facilities as simple as nvl().

The query below gets the same result. It is less general, but it will work for the OP's problem (where there are only parents and children, never "third generation" nodes).

Like my other Answer, it is written so that all columns from the original table (or some relevant subset) can be selected. This is best done with analytic functions, as I did in the other Answer as well.

with
  orders_table (order_id, parent_order_id) as (
    select 1, null from dual union all
    select 2, null from dual union all
    select 3, null from dual union all
    select 4, null from dual union all
    select 5, 1    from dual union all
    select 6, 2    from dual union all
    select 7, 3    from dual union all
    select 8, 3    from dual
  )
select order_id, parent_order_id
from   (
         select o.* 
              , max(order_id) over 
                     (partition by coalesce(parent_order_id, order_id)) as max_id
         from   orders_table o
       )
where order_id = max_id
;

Answer 2

Here is one way to solve this problem, using a connect by query (which is, in fact, a form of join - optimized for hierarchical data, like yours). The WITH clause is not part of the solution, it is there only to simulate your inputs. Use your actual table and column names.

Note that I also get the row with order_id = 5 in the results (I asked about that in my comments, you answered other questions but not this one).

This shows how to get ALL the columns you need in one pass.

with
  orders_table (order_id, parent_order_id) as (
    select 1, null from dual union all
    select 2, null from dual union all
    select 3, null from dual union all
    select 4, null from dual union all
    select 5, 1    from dual union all
    select 6, 2    from dual union all
    select 7, 3    from dual union all
    select 8, 3    from dual
  )
select order_id, parent_order_id
from   (
         select     o.*
              ,     max(order_id)
                        over (partition by connect_by_root order_id) as max_id
         from       orders_table o
         where      connect_by_isleaf = 1
         start with parent_order_id is null
         connect by parent_order_id = prior order_id
       )
where  order_id = max_id
;

ORDER_ID  PARENT_ORDER_ID
--------  ---------------
       5                1
       6                2
       8                3
       4