Jump to content

Order By statement not working as spected...


ruano84

Recommended Posts

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

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..........................

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.