1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sorting on a field which keeps the parent child relation

Discussion in 'SQL PL/SQL' started by ASHLY THOMAS, Jul 31, 2016.

  1. ASHLY THOMAS

    ASHLY THOMAS Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Dubai
    I have the following data in my database table.
    Code (Text):

    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:

    Code (Text):

    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?
     
    Last edited: Jul 31, 2016
  2. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    1. with t as
    2. (
    3. select 100 rec_id, 'Record 1' description, 1000 parent_id, '80.160.4.5' ip_address, 1 is_parent from dual unionall
    4. select 101 rec_id, 'child ' description, 1000 parent_id, '169.60.5.2' ip_address, 0 is_parent from dual unionall
    5. select 105 rec_id, 'child ' description, 1000 parent_id, '169.60.5.2' ip_address, 0 is_parent from dual unionall
    6. select 103 rec_id, 'Record 2' description, 1002 parent_id, '80.160.4.3' ip_address, 1 is_parent from dual unionall
    7. select 104 rec_id, 'child ' description, 1002 parent_id, '169.60.2.5' ip_address, 0 is_parent from dual unionall
    8. select 102 rec_id, 'Record 3' description, 1001 parent_id, '80.160.4.2' ip_address, 1 is_parent from dual
    9. )
    10. select rec_id, description, parent_id, ip_address, is_parent
    11. from (
    12. select t1.*
    13. max(rno) keep(dense_rank lastorderby is_parent) over(partition by parent_id) rno1
    14. from (
    15. select t.*, row_number() over(partition by is_parent orderby ip_address) rno
    16. from t
    17. ) t1
    18. )
    19. order
    20. by rno1, is_parent desc;