0

Let's say I'm trying to sort the Column "numbers"

numbers =
"1,
1.1,
1.1.1,
1.1.2,
1.1.10"

When I do

ORDER BY NUMBERS , the resulting sorted column is

numbers =
"1,
1.1,
1.1.1,
1.1.10,
1.1.2"

I know the original column is already sorted, but that's only for this example... Why does .10 show up before .2?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Jeffrey Doucet
  • 1
  • 1
  • 3
    (assuming the numbers' column type is text). Because 1.1.10 is before 1.1.2 in the alphabetical order. Quoting wikipedia: The result of placing a set of words or strings in alphabetical order is that all the strings beginning with the same letter are grouped together; and within that grouping all words beginning with the same two-letter sequence are grouped together; and so on. The system thus tends to maximize the number of common initial letters between adjacent words. – vc 74 Oct 17 '14 at 12:35
  • 1
    @vc74 - i would post that as an answer... because that's all the question really asks, why? not necessarily how to fix or change. – gloomy.penguin Oct 17 '14 at 12:39
  • @gloomy.penguin Dun! – vc 74 Oct 17 '14 at 12:40
  • If you want a solution: [How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query](http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query) – Alex K. Oct 17 '14 at 12:47

2 Answers2

1

(assuming the numbers' column type is text).

Because 1.1.10 is before 1.1.2 in the alphabetical order.

Quoting wikipedia: The result of placing a set of words or strings in alphabetical order is that all the strings beginning with the same letter are grouped together; and within that grouping all words beginning with the same two-letter sequence are grouped together; and so on. The system thus tends to maximize the number of common initial letters between adjacent words.

vc 74
  • 37,131
  • 7
  • 73
  • 89
0

It's a hack, but you can try this:

ORDER BY CAST(PARSENAME(numbers, 3) as int), CAST(PARSENAME(numbers, 2) as int), CAST(PARSENAME(numbers, 1) as int)
Svein Fidjestøl
  • 3,106
  • 2
  • 24
  • 40