Oracle 9 Query question...

Hello,

I am no Query expert so I would appreciate it very much if someone could help me out. We use Oracle 9.

I have 1 table named contents.
This table has the following fields:

NR		number(9)		NOT NULL	- the unique identifier
PARENT		number(9)		NOT NULL	- the NR of the item which is the parent
of this item (child)
FOLDER		number(1)		NOT NULL	- Boolean defining if the item is capable
of having children
TITLE		varchar2(256)	NOT NULL	- the title of the item
SORTINDEX	number(3)			- the value that defines the order of items
CONTENTTYPE	number(5)		NOT NULL	- a value for a related table

The table looks like this (more or less) NR 56018 is the root which is not important for this example:

NR	PARENT	FOLDER	TITLE		SORTINDEX	CONTENTTYPE
55580       56018	1	Home		0		644
55581	55580	1	First		0		644
55582	55580	1	Second		1		644
55583	55580	1	Third 		2		644
55584	55580	0	Fourth 		3		644
55585	55581	0	First - 1		0		644
55586	55581	0	First - 3		1		644
55587	55581	1	First - 2		2		644
55588	55582	0	Second - 1		0		644
55589	55583	1	Third - 1		0		644
55590	55583	0	Third - 2		1		644
55591	55587	0	First - 2 - 1	0		644
55592	55589	0	Third - 1 - 1	0		644

No problems with the straight forward queries... but in this particular case I would like to make a query that returns a list of items ordered by the value in sortindex and grouped by parentfolder within the range of the contenttype 644.

Which output should look something like this (hyphens show with folders, square-brackets show with items): Home (55580)

    First (55581)

        First - 1 [55585]
        First - 2 (55587)
            First - 2 - 1 [55591]
        First - 3 [55586]
    Second (55582)
        Second - 1 [55588]
    Third (55583)
        Third - 1 (55589)
            Third - 1 - 1 [55592]
        Third - 2 [55590]

Who could help me with the query on this? I can't order on either of the fields to render this list, the only solution I came up with was trying to build a row number with 'sortorder of parent's parent'+'sortorder of child's parent'+'sortorder of child'... but I don't have a clue on how to build that. Or is there a much easier way? (Please say yes and show me :)

The actual list must be flexible in depth so the example above only shows 3 layers but it could be as much as 9 layers... I really really hope you can help me solve this riddle, Thanks in advance...

M_at_r(o Received on Thu Jan 10 2008 - 07:37:36 CST