Click here to Skip to main content
14,766,249 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want order hierarchyid field by asc, but i dont know how cast it to int
select Path.ToString() as Nomer ,
        Path,
        Name,
        Path.GetLevel() as Axixa

from Users order by cast(Path as nvarchar(100)) ASC;



/1/1/
/1/2/
/1/3/
/2/1/
/2/2/
/2/3/
-/214/1/-
-/214/2/-
/3/1/1/
/3/1/2/

should be so

/1/1/
/1/2/
/1/3/
/2/1/
/2/2/
/2/3/
/3/1/1/
/3/1/2/
/214/1/
/214/2/
Posted
Updated 13-Apr-15 3:02am
v2
Comments
Nathan Minier 13-Apr-15 8:13am
   
Is there a compelling reason that you're trying to cast Path as nvarchar, when you're already selecting Nomer as a string value? Why not just ORDER BY Nomer?

No need to convert the column - just order by [Path], and the HierarchyID type will do the right thing:
SELECT
   Path.ToString() as Nomer,
   Path,
   Name,
   Path.GetLevel() as Axixa
FROM
   Users
ORDER BY
   Path
;

/*
Output:

/1/1/
/1/2/
/1/3/
/2/1/
/2/2/
/2/3/
/3/1/1/
/3/1/2/
/214/1/
/214/2/
*/

http://sqlfiddle.com/#!6/08c3e/1[^]
   
Comments
Maciej Los 13-Apr-15 16:19pm
   
5ed!
You can use functions in the order by to help out. Here it assumes your largest path is 3 characters. If you like more add more 0's and change the 3's to the number of 0's.

Good Luck

ORDER BY RIGHT('000'+CAST(Path AS VARCHAR(3)),3) ASC
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900