CODEWITHSUNDEEP
mysqlsqlsql-servert-sql
user2232273
user2232273

Reputation: 4964

Naturally ORDER a column containing hierarchical item names

I have a column (of VARCHAR type) with values like these:

1
2
3
1.1
1.1.1
1.2.1
5
4
7
8
9
10
10.2
10.1

I hop[e to select this column and order it naturally, like the following:

1
1.1
1.1.1
1.2.1
2
3
4
5
...

I have tried ordering it with this for example and a lot of other query

SELECT data 
FROM sample 
ORDER BY LEN(data), data

Does someone have an idea how to do this?

Upvotes: 2

Views: 106

Answers (3)

Anoo S Pillai
Anoo S Pillai

Reputation: 46

The data seems something like Hierarchical. With the current set of data supplied, if data is converted to hierarchical Data the order by can be done using something similar to:-

SELECT data FROM sample ORDER BY CAST ( '/' + replace( data, '.', '/' ) + '/' as hierarchyid )

Upvotes: 1

O. Jones
O. Jones

Reputation: 108651

You seem to be hoping to order a series of hierarchically named items in a natural order. It looks like these items' names take the form.

 token [ .token [. token [ .token ]]]

where subsequent tokens after the first are optional.

I suppose you want each token, if it's numeric, to be handed as a number. That is, for example, you want 1.123 to come after 1.2 because 123 is numerically greater than 2.

You didn't say what you want done with alphabetical tokens, e.g. 401.k and 403.b. I suppose they should come after the numerical ones, but in lexical order.

This query (http://sqlfiddle.com/#!2/81756/2/0) will do the trick out to five hierarchical levels of tokens.

SELECT col
  FROM T 
 ORDER BY
       FLOOR(SUBSTRING_INDEX(col,'.',1)),
       SUBSTRING_INDEX(col,'.',1),

       FLOOR(SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',1)))),
       SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',1))),

       FLOOR(SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',2)))),
       SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',2))),

       FLOOR(SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',3)))),
       SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',3))),

       FLOOR(SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',4)))),
       SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',4)))

Why does this work? FLOOR() converts the leftmost part of a string to an integer, so it picks up the leading integer. If it doesn't find any numbers in the string it's trying to convert, it returns zero.

And, SUBSTRING(col, 2+LENGTH(SUBSTRING_INDEX(col,'.',NNN))) picks up the part of the col item to the right of the NNNth dot.

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

try this

   ORDER BY data, LEN(data)

or this

 ORDER BY CONVERT(SUBSTRING_INDEX(data, ',', -1), SIGNED), Len(data)

i give demo in mysql as tsql there is not in sqfiddle .

DEMO

Upvotes: 2

Related Questions