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

Itzik Ben-Gan
6/5/2006 9:06:30 PM

Andreas,

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.

[quoted text, click to view]

Andreas Klemt
6/5/2006 11:10:23 PM

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

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