Please help - setting order of select in a self-referencing table

Hello,
I have a table of product categories that looks like this (air code, so
could be some typos, but it's basically right)...
create table producttypes (
ptype int not null identity(1,1) primary key,
typename varchar(50) not null default '',
ParentCat int references producttypes(ptype)
)
This allows categories to contain subcategories and so on.
In order to allow me to see the hierarchy, I am using the following
code, modified from some found in "Inside SQL Server 7.0" by Kalen
Delaney.
-- SQL starts
declare @.level int, @.current int
create table #stack (depthlevel int, ptype int)
create table #orgchart (seqno int identity, orglevel int not null, ptype int
not null)
set rowcount 1
select @.level=1, @.current=ptype from producttypes where ptype=parentcat
set rowcount 0
insert into #stack (depthlevel, ptype) values (@.level, @.current)
while (@.level>0)
begin
if exists (select * from #stack where depthlevel=@.level)
begin
set rowcount 1
select @.current=ptype from #stack where depthlevel=@.level
set rowcount 0
insert into #orgchart (orglevel, ptype) select @.level, @.current
delete from #stack where depthlevel=@.level and ptype=@.current
insert into #stack select @.level+1, ptype from producttypes where parentcat=
@.current and parentcat<>ptype
if @.@.ROWCOUNT > 0 select @.level=@.level+1
end
else
select @.level=@.level-1
end
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno
drop table #stack, #orgchart
-- SQL ends
This produces a set of results that gives me all the categories. I can
use the orglevel field to tell what level I'm at, so if it changes, I
can see if I've gone up or down the hierarchy.
Now, the problem is that I can't work out how to set the order of
categories. For example, if the above SQL produces...
1 16 16 Products
2 17 16 Outdoor Toys
3 1 17 Trampolines
3 2 17 Slides
3 3 17 Swings
3 4 17 Accessories
3 5 17 Climbing Frames
3 7 17 Bicycles
you can see that the categories on level 3 are not in any obvious order.
I would like to have them in alphabetical order.
Any ideas how I would modify the above SQL to do this? TIA
Alan Silver
(anything added below this line is nothing to do with me)Try
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
The results should be sorted first by the orglevel, and results within the
same orglevel will be sorted alphabetically. The syntax reads like "order
results first by o.seqno, then by e.typename"
"Alan Silver" wrote:

> Hello,
> I have a table of product categories that looks like this (air code, so
> could be some typos, but it's basically right)...
> create table producttypes (
> ptype int not null identity(1,1) primary key,
> typename varchar(50) not null default '',
> ParentCat int references producttypes(ptype)
> )
> This allows categories to contain subcategories and so on.
> In order to allow me to see the hierarchy, I am using the following
> code, modified from some found in "Inside SQL Server 7.0" by Kalen
> Delaney.
> -- SQL starts
> declare @.level int, @.current int
> create table #stack (depthlevel int, ptype int)
> create table #orgchart (seqno int identity, orglevel int not null, ptype i
nt not null)
> set rowcount 1
> select @.level=1, @.current=ptype from producttypes where ptype=parentcat
> set rowcount 0
> insert into #stack (depthlevel, ptype) values (@.level, @.current)
> while (@.level>0)
> begin
> if exists (select * from #stack where depthlevel=@.level)
> begin
> set rowcount 1
> select @.current=ptype from #stack where depthlevel=@.level
> set rowcount 0
> insert into #orgchart (orglevel, ptype) select @.level, @.current
> delete from #stack where depthlevel=@.level and ptype=@.current
> insert into #stack select @.level+1, ptype from producttypes where par
entcat=@.current and parentcat<>ptype
> if @.@.ROWCOUNT > 0 select @.level=@.level+1
> end
> else
> select @.level=@.level-1
> end
> select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
> join producttypes as e on e.ptype=o.ptype order by o.seqno
> drop table #stack, #orgchart
> -- SQL ends
>
> This produces a set of results that gives me all the categories. I can
> use the orglevel field to tell what level I'm at, so if it changes, I
> can see if I've gone up or down the hierarchy.
> Now, the problem is that I can't work out how to set the order of
> categories. For example, if the above SQL produces...
> 1 16 16 Products
> 2 17 16 Outdoor Toys
> 3 1 17 Trampolines
> 3 2 17 Slides
> 3 3 17 Swings
> 3 4 17 Accessories
> 3 5 17 Climbing Frames
> 3 7 17 Bicycles
> you can see that the categories on level 3 are not in any obvious order.
> I would like to have them in alphabetical order.
> Any ideas how I would modify the above SQL to do this? TIA
> --
> Alan Silver
> (anything added below this line is nothing to do with me)
>|||>Try
>select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
>join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
>The results should be sorted first by the orglevel, and results within the
>same orglevel will be sorted alphabetically. The syntax reads like "order
>results first by o.seqno, then by e.typename"
Mark,
Thanks for the reply, but if you look carefully, it's not that simple.
Your suggestion will list all level 2 categories together, followed by
all level 3 and so on. This will not give the correct hierarchy as there
may be several level 2 categories each with subcategories (ie level 3).
If this isn't clear, look at the longer category listing shown later on.
This is a more complete list than the abbreviated one I showed before.
Note that the ordering is done on seqno, which is the order in which the
categories were put into the orgchart table. The ordering is not done on
the level at all.
I think I need to modify the way the categories are pulled out of the
producttypes table when they are inserted into the stack table. Trouble
is, I can't see how to control the ordering as they are being pulled one
at a time.
Thanks for the reply. Any further help would be appreciated.
Longer category listing follows...
level typename
1 Products
2 Outdoor Toys
3 Trampolines
3 Slides
3 Swings
3 Accessories
3 Climbing Frames
3 Bicycles
2 Indoor Toys
3 Snooker Tables
3 Boy's Toys
4 Hard Puzzles
3 Girl's Toys
4 Easy Puzzles
3 Board Games
3 Pets
4 Ferrets
4 Dogs
4 Cats
>"Alan Silver" wrote:
>
Alan Silver
(anything added below this line is nothing to do with me)sql