For reporting purposes we would generally want to sort all the nodes within one parent by node name. Below is an extension to the subordinates function that returns a field suitable for this purpose. The added field is called "node_sort" so your query might look like this...

select * from subordinates(2007,1) order by node_sort, node_name

----- create Subordinates function using CTE with added sort field

if object_id( 'subordinates', 'IF' ) is not null drop function subordinates

GO

create function subordinates( @year int, @node_id int ) returns table as return

with subnodes( distance, year, node_id, parent_node, node_name, node_seq, node_sort) as

(

select 0, @year, h.node_id, h.parent_node, h.node_name,

convert( varchar(99), ltrim(str(h.node_id))) as node_seq,

convert( varchar(99), '')

from hierarchy h where h.node_id = @node_id and h.year = @year

union all

select distance+1, @year, h.node_id, h.parent_node, h.node_name,

convert( varchar(99), sn.node_seq + '.' + ltrim(str(h.node_id))),

convert( varchar(99), sn.node_sort + '.' + right('00000' + cast(isnull(h.parent_node,'0') as varchar(5)), 5) )

from hierarchy h inner join subnodes sn on h.year = @year and h.parent_node = sn.node_id

)

select distance, year, node_id, parent_node, node_name, node_seq, node_sort from subnodes

GO