SELECT * FROM table_name ORDER BY CAST(field_name as SIGNED INTEGER) ASC
Contoh:
SELECT * FROM customer ORDER BY CAST(sale as SIGNED INTEGER) ASC
***
Actually i’ve found something interesting:
SELECT * FROM mytable ORDER BY LPAD(LOWER(mycol), 10,0) DESC
This allows you to order the field like:
1
2
3
10
A
A1
B2
10A
111
***
Trick I just learned. Add ‘+0’ to the varchar field order clause:
SELECT * FROM table ORDER BY number+0 ASC
I now see this answer above. I am wondering if this is typecasting the field and an integer. I have not compared performance. Working great.
***
For a table with values like Er353, ER 280, ER 30, ER36 default sort will give ER280 ER30 ER353 ER36
SELECT fieldname, SUBSTRING(fieldname, 1, 2) AS bcd,
CONVERT(SUBSTRING(fieldname, 3, 9), UNSIGNED INTEGER) AS num
FROM table_name
ORDER BY bcd, num;
the results will be in this order ER30 ER36 ER280 ER353
***
you can get order by according to your requirement my using following sql query
SELECT * FROM mytable ORDER BY ABS(mycol)
***
given a column username containing VARCHAR‘s like these:
username1
username10
username100
one could do:
SELECT username,
CONVERT(REPLACE(username, 'username', ''), UNSIGNED INTEGER) AS N
FROM users u
WHERE username LIKE 'username%'
ORDER BY N;
it is not cheap, but does the job.