This code attempts to sort my values by number of digits, from 6 to 7, then from lowest to highest numerically.
SELECT * FROM score ORDER BY ID DESC
I have sets of data in my table and I always get:
| ID |
|---|
| 992425 |
| 992199 |
| 1001111 |
I expect result
| ID |
|---|
| 992199 |
| 992425 |
| 1001111 |
How to sort by number of digits, from 6 to 7, then ID from lowest to highest numerically?
The ID column is an NVARCHAR column.
Advertisement
Answer
As you have your integer data stored in nvarchar column there are chances of cast getting failed due to other than integer value in your column.
I would suggest using try_cast as follows:
select * from score ORDER BY try_cast(ID as int)