OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Sorting (12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sorting [message #654338]

Sun, 31 July 2016 05:46

ashlythomas
Messages: 1
Registered: July 2016

Junior Member

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 on achieving this?

Report message to a moderator

Re: Sorting [message #654340 is a reply to message #654338]

Sun, 31 July 2016 09:46

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

You could use min(ip_address) over (partition by parent_id) order by that then by ip_address assuming that the parent id will always have the lowest ip within the group.
Fraid I can't test that at the moment as I'm answering on my phone.

[Updated on: Sun, 31 July 2016 09:53]

Report message to a moderator

Re: Sorting [message #654341 is a reply to message #654340]

Sun, 31 July 2016 09:55

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Here is a spoon feeding solution, slightly different from yours:

https://community.oracle.com/thread/3956817

Report message to a moderator

Re: Sorting [message #654342 is a reply to message #654338]

Sun, 31 July 2016 11:52

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Sorting [message #654346 is a reply to message #654338]

Sun, 31 July 2016 14:00

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

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.

Report message to a moderator