Page 1 of 1

sorting varchar elements

Posted: Thu Aug 12, 2004 12:21 am
by bugthefixer
i have a fiel named station_code whose datatype is varchar
most of the values are numerical only a few are aplhabetical like a-1 a2 etc.

now i want to sort it in asc order..i have tried but it sorts lik this
1
10
100
11
111
2
21
212

but it shud sort like this
1
2
10
11
21
and so on

anybody cud tell me how to solve this problem

the query i m using is this

Code: Select all

<?php
mysql_query("select train_name,train_code,direction from train where defined='Y' order by tain_code asc");



?>

Posted: Thu Aug 12, 2004 12:53 am
by feyd
I found this an interesting challenge, but here's a solution I found:

Code: Select all

SELECT  * 
FROM  `train` 
WHERE `train_code` REGEXP '^&#1111;0-9]+$'
UNION(
SELECT * FROM `train`
WHERE `train_code` REGEXP '&#1111;^0-9]'
ORDER  BY LENGTH( `train_code` ) , `train_code`
)
edit: ok, I thought it was working.. hmm

Posted: Thu Aug 12, 2004 1:09 am
by bugthefixer
well thanx feyd i have tried it but it gives error on UNION
anyhow i did manage to get required result from this
but it only displays numerical values
i want all the values to be printed
if the value is alpha-numeric then it shud be printed at end

Posted: Thu Aug 12, 2004 1:16 am
by feyd
you'll need a double query then, if you can't perform a union.. or a really oddly configured join.. lemme fiddle around..

Posted: Thu Aug 12, 2004 1:52 am
by feyd
ok.. here's two that don't use unions.. however, both require a bit of processing. The first, uses double the memory, but only requires you to check if a column is null. The other, you would need a small regex to determine if you want to display it first or second pass.

Code: Select all

SELECT  a.`train_name` `train_name1`, a.`train_code` `train_code1`, a.`direction` `direction1`, b.`train_name` `train_name2`, b.`train_code` `train_code2`, b.`direction` `direction2`
FROM  `train` a
LEFT JOIN `train` b
ON b.val REGEXP '&#1111;^0-9]'
AND b.id = a.id
ORDER BY CHAR_LENGTH(a.val), a.val

----------

SELECT  a.`train_name`, a.`train_code`, a.`direction`
FROM  `train` a
ORDER BY CHAR_LENGTH(a.`train_code`), a.`train_code`
I cannot get it to sort the way you'd like it to, without unions or double queries..

Posted: Fri Aug 13, 2004 12:53 am
by bugthefixer
thanx feyd..

Posted: Fri Aug 13, 2004 9:30 am
by pickle
The sorting method you're looking for is natural sort - which means to sort like a human would, not like a computer would. Since you're calling it from PHP, you can access the PHP function [php_man]natsort[/php_man]. Once you retrieve the data, put that column in an array, then natsort() it. It'll be a bit of work to get the other data sorted along with it, but I'm sure it's doable.