CODEWITHSUNDEEP
sql-server-2008
gordon613
gordon613

Reputation: 2952

Sort by hierarchyid in SQL Server

I have a hierarchyid column defined on a table in SQL Server 2008

Let us say that in the first row, the hierarchyid is '/1/7/1/'

Let us say that in the second row, the hierarchyid is '/1/10/1/'

If I sort by hierarchyid ASC , then I will see the second row, and then the first row. (The sorting will be sort by String, and '10'<'7')

However I have (for compatability reasons with a different system) a wish to see the first row first, and then the second row (I.e. sort by int, and 7<10)

I have solved the problem, by defining a second hierarchyid column, and then setting it to be the same as the first hierarchyid column, but replacing all inside slashes with dots, and then doing a sort by this.

I just wondered if there was a more elegant way.

Upvotes: 1

Views: 2292

Answers (2)

Andy Davies
Andy Davies

Reputation: 706

I know this is a fairly old question, but it was first result in Google so thought I'd add an actual answer in case someone else comes across this. Without seeing the SQL being used it's hard to be 100% but I suspect that the OP is returning the hierarchy Id as a string and sorting on that rather than sorting on the hierarchy id itself:

EG..

declare @results table (Id int, Hierarchy hierarchyId)

-- :
-- Add your data here
-- :

-- This will not work as it's ordering a string
select Id, Hierarchy.ToString() as SortOrder from @results order by SortOrder

-- This will work as it's ordering the hierarchy id
select Id, Hierarchy.ToString() as SortOrder from @results order by Hierarchy

Upvotes: 2

Diego
Diego

Reputation: 36166

you would need to isolate whats between the two "/" and order by it. you can use this function: http://www.sqlusa.com/bestpractices2005/nthindex/

to get the nth Index on a string, so 

declare @aux_str varchar(50)
set @aux_str='/1/7/3/'

select dbo.fnNthIndex(@aux_str,'/',2)

returns 3. Them you have to find out the position of the third "/" and get what's between it.

Its not hard, but its quite a lot of work

Upvotes: 0

Related Questions