distal-attribute
distal-attribute
distal-attribute
distal-attribute

ALTER TABLE table_name ORDER BY cast(column_name AS UNSIGNED)

User, date Message
Written by stalwil
2 years ago
Category: Running SQL scripts
2 posts since Thu, 21 Feb 13
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
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
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
Written by stalwil
2 years ago
2 posts since Thu, 21 Feb 13
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
Written by kalvaro
2 years ago
595 posts since Thu, 29 Nov 07
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.