SELECT the parent of a record

Question

I have this requirement that I need to attach a new column in select statement. It holds the disp_order of the parent level.

I currently have this sql statement

SELECT DISTINCT order_no, 
                code, 
                disp_order, 
                lvl, 
                description 
           FROM tbl_pattern

+----------+------+------------+-----+-------------+
| order_no | code | disp_order | lvl | description |
+----------+------+------------+-----+-------------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |
| RM001-01 | 1    | 2          | 3   | HK140904-1B |
| RM001-01 | 1    | 3          | 4   | HK140904-1C |
| RM001-01 | 1    | 4          | 5   | HK140904-1D |
| RM001-01 | 1    | 5          | 2   | HK140904-1E |
| RM001-01 | 1    | 6          | 3   | HK140904-1E |
| RM001-01 | 1    | 7          | 3   | HK140904-1X |
| RM001-01 | 1    | 8          | 4   | HK140904-1E |
| RM001-01 | 1    | 9          | 5   | HK140904-1E |
+----------+------+------------+-----+-------------+

parent column does not exist on table, but I want to get the disp_order of the level higher than the current record.

From the example table, the result should be like this:

+----------+------+------------+-----+-------------+--------+
| order_no | code | disp_order | lvl | description | parent |
+----------+------+------------+-----+-------------+--------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |        |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |    0   |
| RM001-01 | 1    | 2          | 3   | HK140904-1B |    1   |
| RM001-01 | 1    | 3          | 4   | HK140904-1C |    2   |
| RM001-01 | 1    | 4          | 5   | HK140904-1D |    3   |
| RM001-01 | 1    | 5          | 2   | HK140904-1E |    0   |
| RM001-01 | 1    | 6          | 3   | HK140904-1E |    5   |
| RM001-01 | 1    | 7          | 3   | HK140904-1X |    5   |
| RM001-01 | 1    | 8          | 4   | HK140904-1E |    7   |
| RM001-01 | 1    | 9          | 5   | HK140904-1E |    8   |
+----------+------+------------+-----+-------------+--------+

Data representation through hierarchy of sample provided:

Your data doesn't make a lot of sense. There is nothing that says disp_order 8 should have 6 as a parent, other than maybe it's description. — Based, Sep 28 '18 at 11:23
Why does #9 map to a parent of 7? It has a level of 5 and the previous level 4 row has a display order of 8 so shouldn't that row be its parent? — MT0, Sep 28 '18 at 11:38
@gengencera your data representation should probably show disp_orders and not levels. The levels are represented visually. — Based, Sep 28 '18 at 12:52

MT0 · Accepted Answer · 2018-09-28T12:43:40.597

2

You can use a correlated sub-query to find the most recent disp_order for the previous level:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE tbl_pattern ( order_no, code, disp_order, lvl, description ) AS
SELECT 'RM001-01', 1, 0, 1, 'HK140904-1A' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 1, 2, 'HK140904-1B' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 2, 3, 'HK140904-1B' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 3, 4, 'HK140904-1C' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 4, 5, 'HK140904-1D' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 5, 2, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 6, 3, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 7, 3, 'HK140904-1X' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 8, 4, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 9, 5, 'HK140904-1E' FROM DUAL;

Query 1:

SELECT t.*,
       ( SELECT MAX( disp_order )
         FROM   tbl_pattern p
         WHERE  p.order_no   = t.order_no
         AND    p.code       = t.code
         AND    p.lvl        = t.lvl - 1
         AND    p.disp_order < t.disp_order ) AS parent
FROM   tbl_pattern t

Results:

| ORDER_NO | CODE | DISP_ORDER | LVL | DESCRIPTION | PARENT |
|----------|------|------------|-----|-------------|--------|
| RM001-01 |    1 |          0 |   1 | HK140904-1A | (null) |
| RM001-01 |    1 |          1 |   2 | HK140904-1B |      0 |
| RM001-01 |    1 |          2 |   3 | HK140904-1B |      1 |
| RM001-01 |    1 |          3 |   4 | HK140904-1C |      2 |
| RM001-01 |    1 |          4 |   5 | HK140904-1D |      3 |
| RM001-01 |    1 |          5 |   2 | HK140904-1E |      0 |
| RM001-01 |    1 |          6 |   3 | HK140904-1E |      5 |
| RM001-01 |    1 |          7 |   3 | HK140904-1X |      5 |
| RM001-01 |    1 |          8 |   4 | HK140904-1E |      7 |
| RM001-01 |    1 |          9 |   5 | HK140904-1E |      8 |

edited Sep 28 '18 at 12:43

answered Sep 28 '18 at 11:12

MT0

143,790
11
59
117

disp_order 8 & 9 should be under 6, you have it under 7. It is pretty obscure though. Data would make a lot more sense if 7 was 9 and 8 & 9 were 7 & 8. – Based Sep 28 '18 at 11:16
@PeterPaff Updated so #8 maps to a parent of 6 not 7. However, I cannot see any logic why #9 should map to anything other than a parent of 8 (if it maps to #7 then it should have a level of 4 not 5). Will query the OP on their data. – MT0 Sep 28 '18 at 11:37
I didn't even notice that, but you're right, that doesn't make sense. – Based Sep 28 '18 at 11:52
@MT0 I have updated my sample data. Typographical error. Sorry – gengencera Sep 28 '18 at 12:33
@gengencera Rolled back to my original solution which now matches your updated expected results. – MT0 Sep 28 '18 at 12:44
@MT0 Thanks! Any ideas how can I achieve this at the same time also have the children attached to it, I tried to reverse the logic in this SQL but no luck. – gengencera Oct 12 '18 at 07:00
@gengencera Please ask a new question - add all the necessary details to that question (table structure, sample data, expected output, etc) so that it is a stand-alone question and include a link back to this question so people can see what you are building on. I'll try and answer that question as trying to work out the requirements through comments here is not easy. – MT0 Oct 12 '18 at 07:40
@MT0 I see. I have asked a question and will update it to link it here.Thank you. Btw, here is the link of the new question: https://stackoverflow.com/questions/52773824/recursive-query-in-oracle-to-find-children-and-sibling – gengencera Oct 12 '18 at 07:42

score 1 · Answer 2 · answered Sep 28 '18 at 10:24

Self join your data. Correct join condition is the most important part. Then only use row_number() to filter rows with greatest disp_order for rows were there is more than one match.

^{SQLFiddle demo}

select disp_order, lvl, description, parent
  from (
    select t1.*, t2.disp_order parent, 
           row_number() over (partition by t1.disp_order 
                              order by t2.disp_order desc) rn
      from tbl_pattern t1 
      left join tbl_pattern t2 on t2.lvl = t1.lvl - 1 and t2.disp_order < t1.disp_order)
  where rn = 1
  order by disp_order

disp_order 8 & 9 should be under 6, you have it under 7. It is pretty obscure though. Data would make a lot more sense if 7 was 9 and 8 & 9 were 7 & 8. — Based, Sep 28 '18 at 11:16

SELECT the parent of a record

2 Answers2

Linked