Rescursive Sort Sql Server 2005. How to sort by name in a function (see example)

Hello,
I have this example. My question is, how can I sort after "sortcol" and
"empname" ?
I mean, I get with this function all the nodes sorted but behalft the nodes
I want to
sort to "empname". How can I do this?
WITH NodeTree2(empid, empname, mgrid, depth, sortcol)
AS
(
SELECT empid, empname, mgrid, 0, CAST(empid AS VARBINARY(900))
FROM employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.depth+1, CAST(sortcol + CAST(E.empid
AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE('| ', depth)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
Thanks for helping me!
AndreasAndreas,
Construct the binary sort path out of row numbers based on mgrid
partitioning and empname sorting:
WITH EmpCTE(empid, empname, mgrid, depth, sortcol)
AS
(
SELECT empid, empname, mgrid, 0, CAST(1 AS VARBINARY(MAX))
FROM employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.depth+1,
sortcol + CAST(
ROW_NUMBER() OVER(PARTITION BY E.mgrid ORDER BY E.empname)
AS BINARY(4))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE('| ', depth)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Andreas Klemt" <aklemt68@.hotmail.com> wrote in message
news:%23xtB3ROiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this example. My question is, how can I sort after "sortcol" and
> "empname" ?
> I mean, I get with this function all the nodes sorted but behalft the
> nodes I want to
> sort to "empname". How can I do this?
> WITH NodeTree2(empid, empname, mgrid, depth, sortcol)
> AS
> (
> SELECT empid, empname, mgrid, 0, CAST(empid AS VARBINARY(900))
> FROM employees
> WHERE empid = 1
> UNION ALL
> SELECT E.empid, E.empname, E.mgrid, M.depth+1, CAST(sortcol + CAST(E.empid
> AS BINARY(4)) AS VARBINARY(900))
> FROM Employees AS E
> JOIN EmpCTE AS M
> ON E.mgrid = M.empid
> )
> SELECT
> REPLICATE('| ', depth)
> + '(' + (CAST(empid AS VARCHAR(10))) + ') '
> + empname AS empname
> FROM EmpCTE
> ORDER BY sortcol
> Thanks for helping me!
> Andreas
>