467,868 Members | 1,383 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,868 developers. It's quick & easy.

Tricky SELECT and ORDER BY

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
Jul 20 '05 #1
  • viewed: 1443
Share:
3 Replies
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"

Jul 20 '05 #2
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)
Jul 20 '05 #3
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_rFact.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

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by scott | last post: by
3 posts views Thread by jl | last post: by
1 post views Thread by MorrganMail | last post: by
15 posts views Thread by edouard.spooner | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.