On Mon, 03 Mar 2008 20:28:19 +0100, --CELKO-- <jcelko212@earthlink.net>
wrote:

> Let me re-name things and remove the non-relational auto-numbering
>
> CREATE TABLE Catalog
> (product_name VARCHAR(20) NOT NULL PRIMARY KEY,
> lft INTEGER NOT NULL,
> rgt INTEGER NOT NULL);
>
> INSERT INTO Catalog
> VALUES
> ('ELECTRONICS', 1, 20),
> ('TELEVISIONS', 2, 9),
> ( 'TUBE', 3, 4),
> ('LCD', 5, 6),
> ('PLASMA', 7, 8),
> ('PORTABLE ELECT', 10, 19),
> ('MP3 PLAYERS', 11, 14),
> ('FLASH', 12, 13),
> ('CD PLAYERS', 15, 16),
> ('2 WAY RADIOS', 17, 18);
>
> SELECT X.product_name, X.lvl,
> ROW_NUMBER() OVER (PARTITION BY lvl ORDER BY
> X.product_name) AS sorting
> FROM (SELECT Children.product_name, (COUNT(Parents.product_name) -
> 1) AS lvl
> FROM Catalog AS Children, Catalog AS Parents
> WHERE Children.lft BETWEEN Parents.lft AND
> Parents.rgt
> GROUP BY Children.product_name) AS X (product_name,
> lvl)
> ORDER BY lvl, sorting;
>
> This numbers the items horizontally across the tree:
>
> product name lvl sorting
> ===========================
> ELECTRONICS 0 1
> PORTABLE ELECT 1 1
> TELEVISIONS 1 2
> 2 WAY RADIOS 2 1
> CD PLAYERS 2 2
> LCD 2 3
> MP3 PLAYERS 2 4
> PLASMA 2 5

According to the OP, this is not the desired result, and this particuler
order (at least as far as I see it, you left some rows out, I don't
understand your query fully) could just as easily be obtained by:

SELECT
node.name,
COUNT(parent.lft) - 1 as 'lvl'
FROM nested_category node
LEFT JOIN nested_category parent
ON node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.lft
ORDER BY lvl, node.name

Aside from not having the 'sorting' columns identical ordering, but once
again, not what the OP seems to want.

(Dropped microsoft.public.sqlserver.programming, according to it's syntax
I suspect this is actually the place it originates from, sadly my somewhat
limited news server doesn't carry it though. If someone has both, feel
free do duplicate my posts to that group... And the OP might state what
database he's actually using )
--
Rik Wasmus