ALTER TABLE table_name ORDER BY cast(column_name AS UNSIGNED)

[expired user #6816]'s profile image [expired user #6816] posted 11 years ago in Running SQL scripts Permalink
Hey Guys,

I need your help. I can not get the "CAST" function to work correctly...it keeps giving me an SQL Error (1064): You have an error in your syntax. Am i doing something wrong? I'm not sure why this doesnt work.

ALTER TABLE table_name ORDER BY cast(column_name AS UNSIGNED)

Thanks,
stalwil
kalvaro's profile image kalvaro posted 11 years ago Permalink
It has nothing to do with HeidiSQL. That syntax is just not allowed by MySQL Server:

ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.

ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are permitted as sort criteria; arbitrary expressions are not permitted. This clause should be given last after any other clauses.


https://dev.mysql.com/doc/refman/5.5/en/alter-table.html#idp88030800
[expired user #6816]'s profile image [expired user #6816] posted 11 years ago Permalink
Thanks for the reply!

So what would be the correct syntax to use to order by a column with two digits (ie...1,2,11,25)? It keeps ordering it like this: 11,1,2,25 but i need it to be like this 1,2,11,25.


Thanks,
stalwil
kalvaro's profile image kalvaro posted 11 years ago Permalink
If you store numbers as strings, you'll get alphabetical order when sorting. What else?

Why do you want to use ALTER TABLE ... ORDER BY anyway? Do you have performance issues?

Please login to leave a reply, or register at first.