SELECT * FROM docs order by doc_id :
i want to order same:
Your problem is that you are trying to sort on text and that sorts differently than numbers. If your doc_id's had 15_01, 15_02, etc then it would sort properly. But since it doesn't, you will have to break the last number out to sort on using a combo of substring to get the last part and a cast or convert to change it to a number value.
2 Likes
Thank you for the reply. I tried and found a better way to arrange as follows and it solved the problem.
SELECT * FROM docs ORDER BY Cast ('/' + replace(doc_id, '_', '.') + '/' AS HIERARCHYID)
Hi, this could be a costly sorting operation. I would add a persitent, calculated field to the table, and sort by this field. Probably will be faster.
1 Like