0

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:

1 └2 └3 └4 └5 └2 └3 └3 └4 └5
Improve this question
3
  • 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. Commented Sep 28, 2018 at 11:23
  • 1
    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? Commented Sep 28, 2018 at 11:38
  • @gengencera your data representation should probably show disp_orders and not levels. The levels are represented visually. Commented Sep 28, 2018 at 12:52

2 Answers 2

Reset to default
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 |
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

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.
@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.
I didn't even notice that, but you're right, that doesn't make sense.
@MT0 I have updated my sample data. Typographical error. Sorry
@gengencera Rolled back to my original solution which now matches your updated expected results.
|
1

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
Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.