在sql中排序max到min值无法正常工作

Question

i have 1 table for ex table1 and it have columns names: id, name, amount.

id name amount
1  abc  20
2  xyz  50
3  pqr  246

i want to sort it from max value to min. i have written this query:

SELECT * FROM table1 ORDER BY amount DESC;

but it gives output like this:

id name amount
2  xyz  50
3  pqr  246
1  abc  20

which is not valid output that i want, i want output like this:

id name amount
3  pqr  246
2  xyz  50
1  abc  20

so, if anyone know the solution of this problem please give me the solution.

Answer 1

You are storing the amount as a string, it would appear.

Here are two solutions. You can convert the values to a number for the sort:

order by amount + 0 desc

Or, order by the length first and then the value:

order by length(amount) desc, amount desc

In general, in SQL, you should make use of the built-in types and store values as the appropriate time. Numbers should be stored as numbers (integers, floats, or decimals). Dates and times should be stored as date/time types. And strings can be used for string values.