How to properly sort a materialized-path?

Question

I would like to sort in sql the following path:

Path
11
14
14.5
2
23
3.6

to look like this:

Path
2
3.6
11
14
14.5
23

Guessing you are storing your numerical data as a `varchar`, *not* a numerical data type; that s the problem. Fix your data types, fix the problem. — Thom A, Feb 22 '23 at 11:27
SQL Server has the [hierarchyid](https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16) type for materialized paths, along with functions that can check and find parents, children etc. `hierarhcyid` can be ordered and indexed properly no matter who long the PK value is. To get a `varchar` to work correctly you'd have to force all key values to have the exact same width by padding them with 0s — Panagiotis Kanavos, Feb 22 '23 at 11:33
Does this answer your question? [Sorting tree with a materialized path?](https://stackoverflow.com/questions/2797720/sorting-tree-with-a-materialized-path) — AlwaysLearning, Feb 22 '23 at 11:34
How does the OP *"Buna, DIn Nou"* a `hierarchyid` @BogdanSahlean ? What does that mean? — Thom A, Feb 22 '23 at 11:35
@AlwaysLearning that's not a good duplicate. SQL Server already has a `hierarchyid` type that solves the problem. That question is about PostgreSQL and shows a workaround only — Panagiotis Kanavos, Feb 22 '23 at 11:37
Check this https://learn.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver16 — Bogdan Sahlean, Feb 22 '23 at 11:44

Panagiotis Kanavos · Accepted Answer · 2023-02-22T11:52:58.603

The correct type for materialized paths and hierarchies in general is hierarchyid, not varchar. To get consistent ordering and searching with a text type you'd have to ensure all key values have the exact same length by padding them, eg 000014, 000014.000005, etc.

hierarchyid is a binary type but allows converting from and to string representations. It can be sorted and indexed and there are built-in functions to determine parents, children, levels etc.

This example from the documentation shows how easy it is to use such paths

Given this table

CREATE TABLE SimpleDemo  
(
    Level hierarchyid NOT NULL,  
    Location nvarchar(30) NOT NULL,  
    LocationType nvarchar(9) NULL
);

and data :

INSERT SimpleDemo  
    VALUES   
('/1/', 'Europe', 'Continent'),  
('/2/', 'South America', 'Continent'),  
('/1/1/', 'France', 'Country'),  
('/1/1/1/', 'Paris', 'City'),  
('/1/2/1/', 'Madrid', 'City'),  
('/1/2/', 'Spain', 'Country'),  
('/3/', 'Antarctica', 'Continent'),  
('/2/1/', 'Brazil', 'Country'),  
('/2/1/1/', 'Brasilia', 'City'),  
('/2/1/2/', 'Bahia', 'State'),  
('/2/1/2/1/', 'Salvador', 'City'),  
('/3/1/', 'McMurdo Station', 'City');

The rows can be orderd with a simple ORDER BY. In this case Level is the name of the hierarchyid column :

SELECT 
    CAST(Level AS nvarchar(100)) AS [Converted Level], 
    *   
FROM SimpleDemo ORDER BY Level;

The expression CAST(Level AS nvarchar(100)) converts the hierarchyid path values to their text equivalent.

The examples in Lesson 2: Create and Manage Data in a Hierarchical Table go into more detail, showing how to add nodes at any level, search for ancestors etc.

In the EmployeeOrg table the OrgNode field is the primary key.

CREATE TABLE HumanResources.EmployeeOrg  
(  
   OrgNode hierarchyid PRIMARY KEY CLUSTERED,  
   OrgLevel AS OrgNode.GetLevel(),  
   EmployeeID int UNIQUE NOT NULL,  
   EmpName varchar(20) NOT NULL,  
   Title varchar(20) NULL  
) ;

When we know the hierarhcyId of an existing employee, we can find all subordinates with the IsDescendantOf function:

DECLARE @CurrentEmployee hierarchyid  

SELECT @CurrentEmployee = OrgNode  
FROM HumanResources.EmployeeOrg  
WHERE EmployeeID = 46 ;  

SELECT *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1 ;

Thanks for your answer. I tried to use hierarchyid datatype, but when I try to cast it on the fly I'm getting the following error which doesn't make too much sense for me: SqlHierarchyId.Parse failed because the input string '9.4' is not a valid string representation of a SqlHierarchyId node. — test13, Feb 22 '23 at 12:29
`9.4` isn't a valid hierarchyid literal (ie text value). The format shown in the docs is `/9/4/`. Instead of casting it on the fly, add a new `hierarchyid` field and use that. You can convert the old values with eg `UPDATE MyTable SET node='/' + REPLACE(Path,'.','/') + '/'` where `node` is the `hierarchyId field — Panagiotis Kanavos, Feb 22 '23 at 12:30
Indeed you are right, I thought it would let me use my format as well but it seems like it's not possible. — test13, Feb 22 '23 at 12:37

score 0 · Answer 2 · answered Feb 22 '23 at 12:54

0

Using the window function ROW_NUMBER to generate an id ordered by path can do the trick :

with cte as (
  select row_number() over(order by path) as id, path
  from mytable 
)
select path
from cte
order by id

Demo here

answered Feb 22 '23 at 12:54

SelVazi

10,028
2
13
29

How to properly sort a materialized-path?

2 Answers2