Select hierarchical rows in proper order

Question

How do I fix this query in Oracle?

SELECT t.id, t.code, t.text
FROM our_table t
WHERE EXISTS (
    SELECT 1 
    FROM our_table root
    WHERE root.id = t.root_id
    AND root.code IN ('1', '2')
)
order by group, t.sortcode;

Some rows are root rows — their root_id values are null. Some rows are "subrows" whose root_ids reference some other row's id in the same table

Let me clarify what I mean by group (it's not defined in the query)

A row is, for example, in group 1 if

• it's a root row and its code is 1 OR
• it's a subrow but the root row it references in the root_id column has code of 1

I need

1. Only subrows from group 1 or 2
2. The ordering should be based first based on "group", then on the rows' sortcodes (another column, not related to code)

DDL:

CREATE TABLE our_table
(
    id NUMBER,
    code VARCHAR2(2 CHAR),
    text VARCHAR2(20 CHAR),
    root_id NUMBER,
    sortcode NUMBER(5,0)
);

Given the following data:

INSERT INTO our_table (id, code, text, root_id, sortcode)
VALUES 
   (1, '1', 'textA', NULL, 2),
   (2, '2', 'textB', 4, 1),
   (3, '5', 'textC', 1, 3),
   (4, '2', 'textD', NULL, 2),
   (5, '3', 'textE', NULL, 2),
   (6, '4', 'textF', 5, 2),
   (7, '1', 'textG', 1, 2);

I expect the following output. Subrows from group 1 (ordered by sortcode), then the only subrow from group 2. The subrow from group 3 is not included. Codes of subrows don't matter

Id	Text	Sortcode
7	textG	2
3	textC	3
2	textB	1

The task is weird, but it's what it is. I realize that dependencies between rows in the same table are a bad smell

Answer 1

This seems to do it. It's more straightforward than I thought

SELECT t.id, t.text, t.sortcode
FROM our_table t
JOIN our_table root ON t.root_id = root.id
WHERE root.code IN ('1', '2')
ORDER BY root.code, t.sortcode;