Order By statement not working as spected...

[ruano84]

Hi,

 

Im writing a php application that manage the townhouses in a resort complex. Every unit has a id, and some of them have a numeric-based id, and others an alphanumeric id.

 

When i call the select statement with the "order by" "asc" or "des" option, it orders the records like "win98", i mean:

 

101

102

103

104

10A101

10A102

10A103

110

111

112

 

, and not like "XP" (sorry by the names, but that's the order behavior)

 

101

102

103

104

110

111

112

10A101

10A102

10A103

 

, that i need. There is some way to fix this? i mean, without the need of braking the digits in separate columns.

 

Thanks for the attention, any help would be appreciated,

Atte.

Alexis Rodriguez

[Illusion]

try ORDER BY BINARY

[ruano84]

Thanks, but its the same

[Illusion]

select * from table where col NOT LIKE '%A%' order by col UNION select * from table where col LIKE '%A%' order by col;

may be that is not the easy way of doing it, may be u can try using CAST().

 

Guys is there any other way of doing it simply..........................

[ruano84]

thanks, brother, thats it!!

[fenway]

select * from table where col NOT LIKE '%A%' order by col UNION select * from table where col LIKE '%A%' order by col;

may be that is not the easy way of doing it, may be u can try using CAST().

 

Guys is there any other way of doing it simply..........................

 

Well, mysql does a lexical sort for char columns, which is why you're getting the "win98" output, as you call it.  Illusion's solution is a hack, which works for the specified input, but won't work in the general case.  You could use a regex to find any non-integer values, and then use illusion's idea of the union to merge the sets together.