Sql-server – SQL Server – Select parent row or child row if no parent

It's hard to understand your requirements without seeing the definition of category - Presumably, there is a root_id column in that table?

If there is, perhaps you want this?

;WITH level_1 (category_id, category_name, root_id) AS (
    SELECT category_id, 
        category_name,
        root_id
    FROM dbo.category
    WHERE parent_category_id = 1
    UNION ALL
    SELECT c.category_id,
        category_name,
        root_id
    FROM dbo.category AS c 
    CROSS JOIN level_1 
    WHERE c.parent_category_id = level_1.category_id
)
SELECT category_id
    , category_name
    , root_id 
FROM level_1;

Just FYI, it's good practice to begin a CTE with a semicolon, to specify the schema, and avoid using SELECT *

This is one way to do it:

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
    DROP TABLE #Orders;

CREATE TABLE #Orders (
    OrderID int,
    OldOrderID int,
    Action varchar(10),
    EntryDate date,
    Source char(1)
)

-- Your sample data, plus a couple more rows 
--to demonstrate the recursive query
INSERT INTO #Orders
VALUES 
    (    1   ,     NULL      ,   'Insert'  , '2016-01-12',   'A'),
    (    1   ,     NULL      ,   'Remove'  , '2016-01-13',   'A'),
    (    2   ,     NULL      ,   'Insert'  , '2016-01-12',   'B'),
    (    3   ,     NULL      ,   'Insert'  , '2016-01-12',   'C'),
    (    4   ,       3       ,   'Insert'  , '2016-01-13',   'C'),
    (    4   ,     NULL      ,   'Remove'  , '2016-01-14',   'C'),
    (    5   ,       4       ,   'Insert'  , '2016-01-15',   'C'),
    (    5   ,     NULL      ,   'Remove'  , '2016-01-16',   'C');


WITH SourceData AS (
    -- Crosstab to get all orders on a single row
    -- The assumption is you can get just two actions (insert and remove)
    SELECT OrderId, 
        ParentOrder = MAX(OldOrderID), 
        InsertDate = MAX(CASE WHEN Action = 'Insert' THEN EntryDate END), 
        RemoveDate = MAX(CASE WHEN Action = 'Remove' THEN EntryDate END), 
        Source = MAX(Source)
    FROM #Orders
    GROUP BY OrderId
),
OrderHierarchy AS (
    -- Recursive query to get top OrderId
    SELECT *, 
        OrderID AS TopOrderId
    FROM SourceData
    WHERE ParentOrder IS NULL

    UNION ALL

    SELECT SD.*, OH.TopOrderId
    FROM SourceData AS SD
    INNER JOIN OrderHierarchy AS OH
        ON SD.ParentOrder = OH.OrderID
),
AllOrders AS (
    -- Get MAX(RemoveData) for the order chain
    SELECT *, MaxRemovedDate = MAX(RemoveDate) OVER (PARTITION BY TopOrderId)
    FROM OrderHierarchy
)
-- Return just the rows that have no remove date in the chain
SELECT *
FROM AllOrders
WHERE MaxRemovedDate IS NULL;