Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sorting which keeps the parent child structure

3288275Jul 31 2016 — edited Aug 1 2016

I have the following data in my database table.

rec_id        description        parent_id    ip_address        is_parent

100        Record 1        1000        80.160.4.5          1

101        child               1000        169.60.5.20        0

105        child               1000        169.60.5.20        0

103        Record 2        1002        80.160.4.3          1

104        child               1002        169.60.2.5          0

102        Record 3        1001        80.160.4.2          1

I want a sorting based on the column: ip_address of records where is_parent = 1

The parent record should be listed first and the corresponding child should list immediately after the parent.

The result should be as given below:

rec_id        description        parent_id    ip_address        is_parent

102        Record 3        1001        80.160.4.2          1

103        Record 2        1002        80.160.4.3          1

104        child               1002        169.60.2.5          0

100        Record 1        1000        80.160.4.5          1

101        child               1000        169.60.5.20        0

105        child               1000        169.60.5.20        0

Can anyone please help me to achievethe above result?
This post has been answered by AndrewSayer on Jul 31 2016
Jump to Answer

Comments

AndrewSayer
Answer

You need to get the ip address of the parent for each row, you can use an analytic function here max(decode(is_parent,1,ip_address)) over (partition by parent_id)

And then it is trivial to order by is_parent desc:

with ctrl as (
select 100 rec_id,       'Record 1    ' description ,    1000 parent_id, '80.160.4.5  ' ip_address,  1 is_parent from dual union all
select 101 rec_id,       'child       ' description ,    1000 parent_id, '169.60.5.20 ' ip_address,  0 is_parent from dual union all
select 105 rec_id,       'child       ' description ,    1000 parent_id, '169.60.5.20 ' ip_address,  0 is_parent from dual union all
select 103 rec_id,       'Record 2    ' description ,    1002 parent_id, '80.160.4.3  ' ip_address,  1 is_parent from dual union all
select 104 rec_id,       'child       ' description ,    1002 parent_id, '169.60.2.5  ' ip_address,  0 is_parent from dual union all
select 102 rec_id,       'Record 3    ' description ,    1001 parent_id, '80.160.4.2  ' ip_address,  1 is_parent from dual)
select *
From ctrl order by max(decode(is_parent,1,ip_address)) over (partition by parent_id), is_parent desc
/

    REC_ID DESCRIPTION   PARENT_ID IP_ADDRESS    IS_PARENT

---------- ------------ ---------- ------------ ----------

       102 Record 3           1001 80.160.4.2            1

       103 Record 2           1002 80.160.4.3            1

       104 child              1002 169.60.2.5            0

       100 Record 1           1000 80.160.4.5            1

       101 child              1000 169.60.5.20           0

       105 child              1000 169.60.5.20           0

Marked as Answer by 3288275 · Sep 27 2020
2 people found this helpful
unknown-7404

If you have hierarchical data use a hierarchical query. The result tree will have the parents and children together.

See the examples queries in the Oracle documentation

https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52335

Hierarchical Query Examples
CONNECT BY Example The following hierarchical query uses 
the CONNECT BY clause to define the relationship between employees and managers:
SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
        203 Mavris                           101
        204 Baer                             101
. . .
1 person found this helpful
Barbara Boehmer 
SCOTT@orcl_12.1.0.2.0> select rec_id, description, parent_id, ip_address, is_parent
  2  from  database_table
  3  start  with is_parent=1
  4  connect by prior parent_id = parent_id
  5          and prior is_parent = is_parent + 1
  6  order  siblings by ip_address
  7  /
    REC_ID DESCRIPTION  PARENT_ID IP_ADDRESS  IS_PARENT
---------- ----------- ---------- ----------- ----------
      102 Record 3          1001 80.160.4.2          1
      103 Record 2          1002 80.160.4.3          1
      104 child            1002 169.60.2.5          0
      100 Record 1          1000 80.160.4.5          1
      101 child            1000 169.60.5.20          0
      105 child            1000 169.60.5.20          0
6 rows selected.
1 person found this helpful
3288275

Thank you Andrew Sayer ....  That was an awesome solution!

1 person found this helpful
Pablolee

@3288275

Whilst your sample data does  not show it, is it possible that in a scenario where you have >1 child record of a specific parent that the IP addresses are different (rather than your current data scenario where both IP addresses for this situation are the same: 169.60.5.20  )     

If so, then you'll need to add another sort to the list (or take a closer look at Barbara's solution)

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 29 2016
Added on Jul 31 2016
#general-database-discussions
5 comments
1,039 views