Home » SQL & PL/SQL » SQL & PL/SQL » Sorting (12c)
Sorting [message #654338] Sun, 31 July 2016 05:46 Go to next message
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?
Re: Sorting [message #654340 is a reply to message #654338] Sun, 31 July 2016 09:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8559
Registered: January 2010
Location: Global Village
Senior Member
Here is a spoon feeding solution, slightly different from yours:

https://community.oracle.com/thread/3956817
Re: Sorting [message #654342 is a reply to message #654338] Sun, 31 July 2016 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
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.

Re: Sorting [message #654346 is a reply to message #654338] Sun, 31 July 2016 14:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.
Re: Sorting [message #654374 is a reply to message #654338] Mon, 01 August 2016 07:36 Go to previous messageGo to next message
EdStevens
Messages: 1321
Registered: September 2013
Senior Member
Also asked here:

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

[Updated on: Mon, 01 August 2016 07:36]

Report message to a moderator

Re: Sorting [message #654376 is a reply to message #654374] Mon, 01 August 2016 08:05 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
EdStevens wrote on Mon, 01 August 2016 13:36
Also asked here:

https://community.oracle.com/thread/3957003
Yeah, seems like a homework assignment to me.
Previous Topic: Error An INTO Clause
Next Topic: how to report oracle internal error
Goto Forum:
  


Current Time: Sun Jun 13 15:07:36 CDT 2021