Home » SQL & PL/SQL » SQL & PL/SQL » Order By Trouble
icon5.gif  Order By Trouble [message #220560] Wed, 21 February 2007 04:57 Go to next message
abis123
Messages: 31
Registered: February 2007
Member
I have a table with the following structure:

ID      Course/Meal             Relating ID
1       Starter
2       Main
3       Dessert
4       Garlic Bread            1
5       Prawn Cocktail          1
6       Steak                   2
7       Pizza                   2
8       Sausages                2
9       Chocolate Fudge Cake    3
10      Ice Cream               3


Each 'Relating ID' corresponds to an ID. What I want to do is display the list in this way:

ID      Course/Meal             Relating ID
1       Starter
4       Garlic Bread            1
5       Prawn Cocktail          1
2       Main
6       Steak                   2
7       Pizza                   2
8       Sausages                2
3       Dessert
9       Chocolate Fudge Cake    3
10      Ice Cream               3


With the ID being stated then all the relating ID's below. Its the ordering I am struggling with. Hope someone can help.

[Updated on: Wed, 21 February 2007 05:02]

Report message to a moderator

Re: Order By Trouble [message #220569 is a reply to message #220560] Wed, 21 February 2007 05:14 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
SELECT *
FROM test
ORDER BY nvl(relating_id, id_course)
Re: Order By Trouble [message #220574 is a reply to message #220569] Wed, 21 February 2007 05:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you need an extra clause in the ORDER BY statement
SELECT *
FROM test
ORDER BY nvl(relating_id, id_course), id_course
Re: Order By Trouble [message #220576 is a reply to message #220569] Wed, 21 February 2007 05:20 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
even better...
SELECT *
FROM test
ORDER BY nvl(relating_id, id_course), nvl2(relating_id, id_course, id_course)
Re: Order By Trouble [message #220577 is a reply to message #220560] Wed, 21 February 2007 05:23 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
ignore my last message:
nvl2(relating_id, id_course, id_course) is the same as id_course Embarassed
Re: Order By Trouble [message #220587 is a reply to message #220560] Wed, 21 February 2007 05:37 Go to previous messageGo to next message
abis123
Messages: 31
Registered: February 2007
Member
Thanks very much everyone!
Re: Order By Trouble [message #220590 is a reply to message #220560] Wed, 21 February 2007 05:44 Go to previous messageGo to next message
abis123
Messages: 31
Registered: February 2007
Member
I decided to go with:

SELECT *
FROM test
ORDER BY nvl(relating_id, id_course), id_course

This produces the list I'm after. After viewing this list there is something else I was wondering and that is, can I indent the sub-options e.g.

Course/Meal

Starter
   Garlic Bread
   Prawn Cocktail
Main
   Steak
   Pizza
   Sausages
Dessert
   Chocolate Fudge Cake
   Ice Cream

[Updated on: Wed, 21 February 2007 05:44]

Report message to a moderator

Re: Order By Trouble [message #220592 is a reply to message #220590] Wed, 21 February 2007 06:05 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
try this:

SELECT nvl2(p.relating_id, lpad(course_meal, length(course_meal) + 5), p.course_meal)
FROM pm_test p
ORDER BY nvl(relating_id, id_course), id_course
Re: Order By Trouble [message #220673 is a reply to message #220592] Wed, 21 February 2007 09:28 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You might also want to review the CONNECT_BY option if you have more levels to traverse. I don't have time right now to load your example table and give you a working example, but CONNECT_BY is used specifically for this type of relation structure.

HTH,
Ron
Re: Order By Trouble [message #220683 is a reply to message #220590] Wed, 21 February 2007 09:54 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select id
	, decode(level, 1, title, '  '||title)
	, relating_id
from test_table
connect by relating_id = prior id
start with relating_id is null
order by nvl(relating_id, id), id;
Previous Topic: Flash back Tables
Next Topic: oracle tablespace
Goto Forum:
  


Current Time: Sun Dec 04 08:21:20 CST 2016

Total time taken to generate the page: 0.06891 seconds