Tricky SELECT and ORDER BY

[Martin]

Dear Group

I wonder whether you can push me in a direction on how to design the
following statement. I'm looking for a SELECT with some tricky ORDER
BY.

The database table looks like this:

MenuID TabText SubTabID TabOrderID
------- ----------- ----------- -----------
1 Main 0 1
2 Cars 0 2
3 Boats 0 3
4 Planes 0 4
5 Pick-Ups 2 1
6 Campers 2 2

The result should look like this:

Main
Cars
Pick-Ups
Campers
Boats
Planes

Notice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' and
must appear in the result directly following 'Cars'.

In more detail:
'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and
'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabID
value of an item identifies to what top-level category a subcategory
belongs.
The TabOrderID specifies in what order the items should be sorted,
e.g. 'Pick-Ups' comes first and 'Campers' second.

Thanks very much for your help & efforts!

Martin

[IAPW]

In article <72************ **************@ posting.google. com>,
Martin <th************ @hotmail.com> wrote:

Dear Group

I wonder whether you can push me in a direction on how to design the
following statement. I'm looking for a SELECT with some tricky ORDER
BY [...]

Use the Case keyword in the Order By clause.
--
" We gonna charge, we gonna stomp, we gonna march through the swamp
We gonna mosh through the marsh, take us right through the doors"

[Hugo Kornelis]

On 30 Nov 2004 20:26:25 -0800, Martin wrote:

Dear Group

I wonder whether you can push me in a direction on how to design the
following statement. I'm looking for a SELECT with some tricky ORDER
BY.

The database table looks like this:

MenuID TabText SubTabID TabOrderID
------- ----------- ----------- -----------
1 Main 0 1
2 Cars 0 2
3 Boats 0 3
4 Planes 0 4
5 Pick-Ups 2 1
6 Campers 2 2

The result should look like this:

Main
Cars
Pick-Ups
Campers
Boats
Planes

Notice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' and
must appear in the result directly following 'Cars'.

In more detail:
'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and
'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabID
value of an item identifies to what top-level category a subcategory
belongs.
The TabOrderID specifies in what order the items should be sorted,
e.g. 'Pick-Ups' comes first and 'Campers' second.

Thanks very much for your help & efforts!

Martin

Hi Martin,

The following is untested, as you didn't post CREATE TABLE and INSERT
statements:

SELECT a.TabText
FROM MyTable AS a
LEFT JOIN MyTable AS b
ON b.MenuID = a.SubTabID
ORDER BY b.TabOrderID, a.TabOrderID

Note that this works if you have only one level of subcategories. If the
subcategory of Campers if divided further, my query will fail.

If you need a solution that works with more levels of subcategories, you
should consider using another table design. Your design (the adjacency
list model) is not particularly well suited for relational operations; the
nested sets model is often a better choice for modelling hierarchies.

If you google for "nested sets model Joe Celko", you should find several
explanatory messages on the nested sets model.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Martin]

Dear Hugo

Thanks very much for this detailed reply. It even answered a second
question I hadn't asked yet.

Thanks again for your time & efforts!
Have a nice day!

Martin

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<oc******* *************** **********@4ax. com>...

On 30 Nov 2004 20:26:25 -0800, Martin wrote:

Dear Group

I wonder whether you can push me in a direction on how to design the
following statement. I'm looking for a SELECT with some tricky ORDER
BY.

The database table looks like this:

MenuID TabText SubTabID TabOrderID
------- ----------- ----------- -----------
1 Main 0 1
2 Cars 0 2
3 Boats 0 3
4 Planes 0 4
5 Pick-Ups 2 1
6 Campers 2 2

The result should look like this:

Main
Cars
Pick-Ups
Campers
Boats
Planes

Notice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' and
must appear in the result directly following 'Cars'.

In more detail:
'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and
'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabID
value of an item identifies to what top-level category a subcategory
belongs.
The TabOrderID specifies in what order the items should be sorted,
e.g. 'Pick-Ups' comes first and 'Campers' second.

Thanks very much for your help & efforts!

Martin

Hi Martin,

The following is untested, as you didn't post CREATE TABLE and INSERT
statements:

SELECT a.TabText
FROM MyTable AS a
LEFT JOIN MyTable AS b
ON b.MenuID = a.SubTabID
ORDER BY b.TabOrderID, a.TabOrderID

Note that this works if you have only one level of subcategories. If the
subcategory of Campers if divided further, my query will fail.

If you need a solution that works with more levels of subcategories, you
should consider using another table design. Your design (the adjacency
list model) is not particularly well suited for relational operations; the
nested sets model is often a better choice for modelling hierarchies.

If you google for "nested sets model Joe Celko", you should find several
explanatory messages on the nested sets model.

Best, Hugo