MySQL ORDER BY not working for numbers

Question

ORDER BY is not working properly for numbers,but it's working well if I give for columns containing alphanumeric or only characters. The code which I had given is,

dbResult = dbStatement.executeQuery("SELECT Bid_Number,Vendor_Name,Vendor_Address,Amount,Tax_Percentage,Amount_Aftertax,Expected_Deliverydate,Vendor_Code FROM purchase_bid    where PE_Number='"+PENumber+"' ORDER BY Amount_Aftertax ASC"); 

if in my table I've Amount_Aftertax column values as,

840.0
780.0
3180.0
3810.0

by giving this code,i'm getting result according to this order

3180.0
3810.0
780.0
840.0

Hoping a reply please... Thanks in advance..

Answer 1

It sounds like your column type is still a text column, even though it contains numbers - so they're being sorted lexicographically. If you want them to be treated as numbers, you should make the column type a number too.

This doesn't just affect ordering, of course - all your code is likely to improve when you use appropriate types for your column. Just because you can stick pretty much any data in a string doesn't mean it's a good idea. Choose the data type which most closely matches the real data involved.

Additionally, you shouldn't specify the PENumber value like that - you should use a prepared statement, with a placeholder parameter in the SQL, which you provide the value for separately. Constructing SQL dynamically can lead to SQL Injection vulnerabilities, as well as unnecessary conversions and a poor separation of code from data.