I have numbers saved as VARCHAR
to a MySQL database. I can not make them INT
due to some other depending circumstances.
It is taking them as character not as number while sorting.
In database I have
1 2 3 4 5 6 7 8 9 10...
On my page it shows ordered list like this:
1 10 2 3 4 5 6 7 8 9
How can I make it appear ordered by numbers ascending?
This question is related to
mysql
sql
string
numbers
sql-order-by
Alter your field to be INT instead of VARCHAR.
I can not make them INT due to some other depending circumstances.
Then fix the depending circumstances first. Otherwise you are working around the real underlying issue. Using a MySQL CAST is an option, but it's masking your bad schema which should be fixed.
If you are using AdonisJS and have mixed IDs such as ABC-202, ABC-201..., you can combine raw queries with Query Builder and implement the solution above (https://stackoverflow.com/a/25061144/4040835) as follows:
const sortField =
'membership_id'
const sortDirection =
'asc'
const subquery = UserProfile.query()
.select(
'user_profiles.id',
'user_profiles.user_id',
'user_profiles.membership_id',
'user_profiles.first_name',
'user_profiles.middle_name',
'user_profiles.last_name',
'user_profiles.mobile_number',
'countries.citizenship',
'states.name as state_of_origin',
'user_profiles.gender',
'user_profiles.created_at',
'user_profiles.updated_at'
)
.leftJoin(
'users',
'user_profiles.user_id',
'users.id'
)
.leftJoin(
'countries',
'user_profiles.nationality',
'countries.id'
)
.leftJoin(
'states',
'user_profiles.state_of_origin',
'states.id'
)
.orderByRaw(
`SUBSTRING(:sortField:,3,15)*1 ${sortDirection}`,
{
sortField: sortField,
}
)
.paginate(
page,
per_page
)
NOTES:
In this line: SUBSTRING(:sortField:,3,15)*1 ${sortDirection}
,
Ref 1: You can read more about parameter bindings in raw queries here: https://knexjs.org/#Raw-Bindings Ref 2: Adonis Raw Queries: https://adonisjs.com/docs/4.1/query-builder#_raw_queries
This works for me.
select * from tablename
order by cast(columnname as int) asc
It might help who is looking for the same solution.
select * from tablename ORDER BY ABS(column_name)
I was looking also a sorting fields that has letter prefix. Here is what i found out the solution. This might help who is looking for the same solution.
Field Values:
FL01,FL02,FL03,FL04,FL05,...FL100,...FL123456789
select SUBSTRING(field,3,9) as field from table order by SUBSTRING(field,3,10)*1 desc
SUBSTRING(field,3,9)
i put 9 because 9 is way enough for me to hold max 9 digits integer values.
So the result will be 123456789 123456788 123456787 ... 100 99 ... 2 1
Another way to convert.
If you have string field, you can transform it or its numerical part the following way: add leading zeros to make all integer strings having equal length.
ORDER BY CONCAT( REPEAT( "0", 18 - LENGTH( stringfield ) ) , stringfield )
or order by part of a field something like 'tensymbols13', 'tensymbols1222' etc.
ORDER BY CONCAT( REPEAT( "0", 18 - LENGTH( LEFT( stringfield , 10 ) ) ) , LEFT( stringfield , 10 ) )
The column I'm sorting with has any combination of alpha and numeric, so I used the suggestions in this post as a starting point and came up with this.
DECLARE @tmp TABLE (ID VARCHAR(50));
INSERT INTO @tmp VALUES ('XYZ300');
INSERT INTO @tmp VALUES ('XYZ1002');
INSERT INTO @tmp VALUES ('106');
INSERT INTO @tmp VALUES ('206');
INSERT INTO @tmp VALUES ('1002');
INSERT INTO @tmp VALUES ('J206');
INSERT INTO @tmp VALUES ('J1002');
SELECT ID, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END) IsNum
FROM @tmp
ORDER BY IsNum, LEN(ID), ID;
Results
ID
------------------------
106
206
1002
J206
J1002
XYZ300
XYZ1002
Hope this helps
Another and simple way
ORDER BY ABS(column_name)
Another way, without using a single cast.
(For people who use JPA 2.0, where no casting is allowed)
select col from yourtable
order by length(col),col
EDIT: only works for positive integers
This will handle negative numbers, fractions, string, everything:
ORDER BY ISNUMERIC(col) DESC, Try_Parse(col AS decimal(10,2)), col;
Source: Stackoverflow.com