Own sort algorithm in SQL Server

Question

I have a table that stores hierarchical relationships.They stored as strings like 1.1, 1.1.1, 1.1.2, 1.2 ... Thus,when I need to sort them in hierarchical order I need to invoke my own sort function.Aren't I?But I think it is too long write own effective alghoritm (n*ln(n)).I want to use little function that just compares two values and use it with ORDER BY statement.Can I do this?Are there another ways to achive this goals?Thanks.

Can you pad the individual values out to a fixed length with zeroes, e.g. 0001.0001.0002? — HABO
– HABO, Commented Sep 6, 2012 at 13:09
If each level is the same length then a text sort comes out in the right order. — HABO
– HABO, Commented Sep 6, 2012 at 13:14

HABO · Accepted Answer · 2012-09-06 13:18:27Z

2

Left padding each level with zeroes to a fixed length will allow a straightforward text sort. With your sample values:

0001.0001
0001.0001.0001
0001.0001.0002
0001.0002

answered Sep 6, 2012 at 13:18

HABO

15.9k5 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhivanan · Accepted Answer · 2012-09-06 12:46:52Z

0

Try this

order by
    parsename(col,4)*1,
    parsename(col,3)*1,
    parsename(col,2)*1,
    parsename(col,1)*1

answered Sep 6, 2012 at 12:46

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

1 Comment

Anton Putov Over a year ago

parsname function used for small strings but I can have very 'deep' strings - in advance unknown how 'deep' the hierarchy will be.

Collectives™ on Stack Overflow

Own sort algorithm in SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related