sorting varchar elements

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

sorting varchar elements

Post 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");



?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Last edited by feyd on Thu Aug 12, 2004 1:13 am, edited 1 time in total.
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

Post by bugthefixer »

thanx feyd..
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply