SELECT *
FROM `train`
WHERE `train_code` REGEXP '^ї0-9]+$'
UNION(
SELECT * FROM `train`
WHERE `train_code` REGEXP 'ї^0-9]'
ORDER BY LENGTH( `train_code` ) , `train_code`
)
edit: ok, I thought it was working.. hmm
Last edited by feyd on Thu Aug 12, 2004 1:13 am, edited 1 time in total.
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
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.
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 'ї^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..
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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.