sql - SQLite3 Order by highest/lowest numerical value

Question

I am trying to do a query in SQLite3 to order a column by numerical value. Instead of getting the rows ordered by the numerical value of the column, the rows are ordered alphabetically by the first digit's numerical value.

For example in the query below 110 appears before 2 because the first digit (1) is less than two. However the entire number 110 is greater than 2 and I need that to appear after 2.

sqlite> SELECT digit,text FROM test ORDER BY digit;
1|one
110|One Hundred Ten
2|TWO
3|Three
sqlite>

Is there a way to make 110 appear after 2?

标签： sqlstringsqlitesql-order-by

Answer 1

It seems like digit is a stored as a string, not as a number. You need to convert it to a number to get the proper ordering. A simple approach uses:

SELECT digit, text 
FROM test
ORDER BY digit + 0