6

Sorry about my mistake, I should provide the real sample for the question, my ID included characters inside:

sample code:

select ID from student order by ID

Expected output from mine          but system output
-------------------------          -----------------
JAD.1.99.9                             JAD.1.99.10
JAD.1.99.10                            JAD.1.99.9

and this ID is of nvarchar type.

user2098512
  • 999
  • 1
  • 7
  • 8
  • 2
    The system output is the normal expected behavior when sorting by text values. They are not numbers. Character "1" precedes character "9". – Tim May 17 '13 at 09:42
  • Follow this link may be you will get some idea..http://stackoverflow.com/questions/119730/how-do-i-sort-a-varchar-column-in-sql-server-that-contains-numbers – Pank May 17 '13 at 10:02
  • possible duplicate of [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) – Pondlife May 17 '13 at 14:46

2 Answers2

14

Yesterday there was a similar question where i have learned that you can use hierarchyid for version sorting(if you use at least SQL-Server 2008):

SELECT id 
FROM   student 
ORDER  BY Cast('/' + Replace(id, '.', '/') + '/' AS HIERARCHYID)

DEMO

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Thank Tim...your answer is perfect....its improving knowledge.......in right way... – Pank May 17 '13 at 10:20
  • +1 Also you can use ORDER BY clause without replace function: ORDER BY Cast('/' + id + '/' AS HIERARCHYID). Demo:http://sqlfiddle.com/#!3/e3cb1/13 – Aleksandr Fedorenko May 17 '13 at 11:38
-2

You need to order by the substring of the part after any dot. This is going to be a DB specific SQL query and since you didn't mention which one you're using I can't give a detailed SQL example.

This is how you can cast a decimals values

http://www.w3resource.com/sql/aggregate-functions/avg-decimal-places-using-cast-within-avg.php

The answer from Tim is best..

But I am giving one more solution for order varchar values --

SELECT id 
FROM   student 
ORDER  BY ID desc
Javascript Coder
  • 5,691
  • 8
  • 52
  • 98
  • 2
    That would not return what user2098512 wants –  May 17 '13 at 09:48
  • You cannot reverse the order just because OP's result was ordered wrong. Now you are always sorting descending even if the id's would be `1.99.8` and `1.99.9`. This is also not a version sort. Just test it with `1.1.1` and `2.1` which starts with `2.1`. – Tim Schmelter May 17 '13 at 09:59
  • 2
    Please see this example: http://sqlfiddle.com/#!3/c3fbc/1 to understand why this answer is simply wrong. I cannot understand this was upvoted 3 times. –  May 17 '13 at 10:20