0

I have a bill of material table (MBOM) and I am trying to write a recursive query. The parts are to be displayed under their respective parent in order defined by displayorder. I can't figure out how to get this in the correct order. The query I am using is

WITH tree ( id, parentid, level, displayorder, uom, quantity ) AS (
    SELECT
        id,
        parentid,
        0 as level,
        displayorder,
        uom,
        quantity
    FROM
        MBOM
    WHERE
        parentid = 47

    UNION ALL

    SELECT
        c2.id,
        c2.parentid,
        tree.level + 1,
        c2.displayorder,
        c2.uom,
        c2.quantity
    FROM
        MBOM c2
        INNER JOIN tree ON tree.id = c2.parentid
)
SELECT
    *
FROM
    tree;

This is my raw data.

parentid level id quantity uom displayorder
47 0 17 7.6011 884 10
47 0 6 8.3334 884 20
47 0 19 1 665 30
47 0 55 1 665 40
47 0 102 1 665 50
47 0 103 1 665 60
47 0 104 1 665 70
47 0 58 1 665 80
55 1 41 60 665 10
55 1 4 6 665 20
55 1 45 4 665 40
55 1 2 4 665 50
55 1 1 4 665 60
55 1 115 1 665 70
55 1 118 1 665 80
55 1 142 1 665 90
55 1 119 1 665 100
55 1 125 1 665 110
55 1 359 1 665 130
55 1 164 2 665 140
55 1 155 1 665 150
55 1 160 1 665 160
55 1 67 1 665 170
55 1 57 1 665 180
55 1 123 1 665 190
55 1 106 1 665 120
106 2 6 0.9082 884 10
106 2 92 1 665 30
106 2 131 3 665 40
106 2 372 6 665 20
131 3 130 1 665 10
131 3 12 2 665 20
67 2 66 1 665 10
67 2 5 6 665 20
160 2 114 1 665 10
160 2 5 3 665 20
164 2 163 1 665 10
164 2 12 1 665 20
359 2 6 0.1426 884 10
359 2 41 2 665 20
359 2 71 1 665 30
359 2 141 1 665 40
141 3 140 1 665 10
141 3 12 2 665 20
71 3 70 1 665 10
71 3 5 5 665 20
125 2 6 0.0001 884 10
125 2 93 1 665 30
125 2 161 2 665 40
125 2 374 4 665 20
161 3 162 1 665 10
161 3 12 2 665 20
119 2 6 0.0001 884 10
119 2 65 1 665 30
119 2 129 1 665 40
119 2 121 1 665 50
119 2 374 4 665 20
121 3 12 2 665 20
121 3 120 1 665 10
129 3 128 1 665 10
129 3 12 2 665 20
142 2 42 1 665 10
142 2 144 1 665 20
142 2 146 1 665 30
142 2 148 1 665 40
142 2 150 1 665 50
150 3 405 1 665 20
150 3 149 1 665 10
148 3 405 1 665 20
148 3 147 1 665 10
146 3 405 2 665 20
146 3 145 1 665 10
144 3 405 2 665 20
144 3 143 1 665 10
118 2 6 0.0265 884 10
118 2 41 2 665 20
118 2 117 1 665 30
118 2 81 1 665 40
115 2 6 0.0639 884 10
115 2 41 3 665 20
115 2 116 1 665 30
115 2 111 1 665 40

This is my desired result

parentid level id quantity uom displayorder
0 47 1 665 0
47 1 17 7.6011 884 10
47 1 6 8.3334 884 20
47 1 19 1 665 30
47 1 55 1 665 40
55 2 41 60 665 10
55 2 4 6 665 20
55 2 45 4 665 40
55 2 2 4 665 50
55 2 1 4 665 60
55 2 115 1 665 70
115 3 6 0.0639 884 10
115 3 41 3 665 20
115 3 116 1 665 30
115 3 111 1 665 40
55 2 118 1 665 80
118 3 6 0.0265 884 10
118 3 41 2 665 20
118 3 117 1 665 30
118 3 81 1 665 40
55 2 142 1 665 90
142 3 42 1 665 10
142 3 144 1 665 20
144 4 143 1 665 10
144 4 405 2 665 20
142 3 146 1 665 30
146 4 145 1 665 10
146 4 405 2 665 20
142 3 148 1 665 40
148 4 147 1 665 10
148 4 405 1 665 20
142 3 150 1 665 50
150 4 149 1 665 10
150 4 405 1 665 20
55 2 119 1 665 100
119 3 6 0.0001 884 10
119 3 374 4 665 20
119 3 65 1 665 30
119 3 129 1 665 40
129 4 128 1 665 10
129 4 12 2 665 20
119 3 121 1 665 50
121 4 120 1 665 10
121 4 12 2 665 20
55 2 125 1 665 110
125 3 6 0.0001 884 10
125 3 374 4 665 20
125 3 93 1 665 30
125 3 161 2 665 40
161 4 162 1 665 10
161 4 12 2 665 20
55 2 106 1 665 120
106 3 6 0.9082 884 10
106 3 372 6 665 20
106 3 92 1 665 30
106 3 131 3 665 40
131 4 130 1 665 10
131 4 12 2 665 20
55 2 359 1 665 130
359 3 6 0.1426 884 10
359 3 41 2 665 20
359 3 71 1 665 30
71 4 70 1 665 10
71 4 5 5 665 20
359 3 141 1 665 40
141 4 140 1 665 10
141 4 12 2 665 20
55 2 164 2 665 140
164 3 163 1 665 10
164 3 12 1 665 20
55 2 155 1 665 150
55 2 160 1 665 160
160 3 114 1 665 10
160 3 5 3 665 20
55 2 67 1 665 170
67 3 66 1 665 10
67 3 5 6 665 20
55 2 57 1 665 180
55 2 123 1 665 190
47 1 102 1 665 50
47 1 103 1 665 60
47 1 104 1 665 70
47 1 58 1 665 80
Improve this question
3
  • 2
    While I appreciate you posting the actual and expected data, you've posted waaaaay too much data for anyone to sift through - please reduce your actual+expected data to something minimal, say, a handful of rows (5-10?) that demonstrate the issue in isolation. Commented Jun 27, 2023 at 2:42
  • 3
    Uhm, you don't have an ORDER BY displayorder anywhere in your query... and I can't see how your "expected" data is sorted... I'm not seeing any logical pattern or arrangement. Commented Jun 27, 2023 at 2:44
  • modify your code as SELECT * FROM tree order by parentid,displayorder; . your desire order is based on the parentid,displayorder Commented Jun 27, 2023 at 6:01

1 Answer 1

Reset to default
0

SQL will not guarantee any order without an ORDER BY, so you might want to add one:

WITH tree ( id, parentid, level, displayorder, uom, quantity ) AS (
    SELECT
        id,
        parentid,
        0 as level,
        displayorder,
        uom,
        quantity
    FROM
        MBOM
    WHERE
        parentid = 47

    UNION ALL

    SELECT
        c2.id,
        c2.parentid,
        tree.level + 1,
        c2.displayorder,
        c2.uom,
        c2.quantity
    FROM
        MBOM c2
        INNER JOIN tree ON tree.id = c2.parentid
)
SELECT
    *
FROM
    tree
ORDER BY level, displayorder
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.