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.
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.
Its related to wrong datatype.
Make data type of amount column as INT or FLOAT.
See the design of the table by using a command such as
DESC TABLENAME
I suspect your Amount column is text/varchar and not numeric.
Or simply run the following command and then try your query.
ALTER TABLE TABLE1 ALTER COLUMN AMOUNT INT
Always use appropriate data types for the data to store.
Since the data type of amount column is string and not numeric, ORDER BY sorts it based on the ASCII Value.
SQL> WITH DATA(ID, NAME, amount) AS(
2 SELECT 2, 'xyz', '50' FROM dual UNION ALL
3 SELECT 3, 'pqr', '246' FROM dual UNION ALL
4 SELECT 1, 'abc', '20' FROM dual
5 )
6 SELECT * FROM DATA
7 ORDER BY amount DESC;
ID NAM AMO
---------- --- ---
2 xyz 50
3 pqr 246
1 abc 20
SQL>
If you have only numeric data stored as string, then you could use TO_NUMBER to explicitly convert the string into number while sorting.
ORDER BY to_number(amount) DESC
For example,
SQL> WITH DATA(ID, NAME, amount) AS(
2 SELECT 2, 'xyz', '50' FROM dual UNION ALL
3 SELECT 3, 'pqr', '246' FROM dual UNION ALL
4 SELECT 1, 'abc', '20' FROM dual
5 )
6 SELECT * FROM DATA
7 ORDER BY to_number(amount) DESC;
ID NAM AMO
---------- --- ---
3 pqr 246
2 xyz 50
1 abc 20
SQL>