Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the varchar field named _tid, normally which contains numerical field, but it is also possible that may be contain string field. Now i wanted to to sort the column by numerically rather than lexicographically. Here the query i've used,

mysql> select _tid,_name from teacher order by _tid;

which returns the following row set.

 +------+----------------------+
    | _tid | _name                |
    +------+----------------------+
    | 1    | A.MANIVANNAN         |
    | 10   | M.ELUMALAI           |
    | 100  | SAMPATH.R            |
    | 101  | S.PAULRAJ            |
    | 102  | A.ASHOK KUMAR        |
    | 103  | S.JAYAKUMAR          |
    | 104  | S.CINRAS             |
    | 105  | P.MURUGAN            |
    | 106  | S.VIJAY              |
    | 107  | N.KARTHIKEYAN        |
    | 108  | G.BALAKRISHNAN       |
    | 109  | C.THARANI            |
    | 11   | M.PONNUSAMY          |
    | 110  | J.KANNAN             |
    | 111  | V.MAHENDRAN          |

But i want row set like the following,

+------+----------------------+
| _tid | _name                |
+------+----------------------+
| 1    | A.MANIVANNAN         |
| 2    | A.PONNIVALAVAN       |
| 3    | B.MUTHU              |
| 6    | R.Kumar              |
| 4    | P.RAJAKALI           |
| 5    | N.Shaunmuga Sundram  |
| 8    | M.Balaji             |
| 7    | V.PALANI             |
| 9    | J.RANJITH            |
| 10   | M.ELUMALAI           |
| 11   | M.PONNUSAMY          |
share|improve this question
3  
An idea: pad your values with 0s from the left and sort that. (Have a look at lpad() function.) Otherwise, it doesn't seem to be a good idea to store data of different types in the same field. –  dezso Jul 19 '12 at 13:59
1  
I upvoted @dezso's comment because of 'it doesn't seem to be a good idea to store data of different types in the same field'. Furthermore, if that field is the primary key and you are using innodb, be aware the length of that primary key will be added to every secondary index of your table –  Derek Downey Jul 19 '12 at 20:02
add comment

2 Answers

Two possibilities: Either pad your values with zeros from the left and sort based on that:

SELECT _tid, _name FROM teacher ORDER BY lpad(_tid, 3, '0');
-- choose an appropriate number instead of 3

or cast your values to a number (similar to Aaron W's solution, apart from here I cast explicitly, and that is a clearer solution):

SELECT _tid,_name FROM teacher ORDER BY cast(_tid as decimal);

Be careful if you have non-number strings in your field:

SELECT cast('something' as decimal);
0
SELECT 'something' + 0;
0
share|improve this answer
1  
The second option sorts better with characters in the value (see this) But it generates the following warnings on anything with a non-numeric character: Warning | 1292 | Truncated incorrect DECIMAL value: '1a' –  Derek Downey Jul 19 '12 at 19:58
add comment

I've gotten around this before by adding zero to the column

select _tid,_name from teacher order by _tid + 0

edit

This had me thinking a bit more so I decided to run a simple test for speed. I'm sure the way you choose depends on the values you have in your database and how you want it to sort, but I created the following table and then inserted 100,000 random teacher names. Using a Windows 7 Pro 32-bit, MySQL 5.5.8

CREATE TABLE `teacher` (
  `_tid` varchar(255) NOT NULL,
  `_name` varchar(255) NOT NULL,
  PRIMARY KEY (`_tid`)
) ENGINE=InnoDB;

I ran each query ten times and averaged the query time

select _tid,_name from teacher order by _tid + 0;
-- 0.60ms

SELECT _tid, _name FROM teacher ORDER BY lpad(_tid, 5, '0');
-- 0.68ms

SELECT _tid,_name FROM teacher ORDER BY cast(_tid as decimal);
-- 0.63ms

I'm sure the results varied because I have a couple VM's running along with only having 250MB - 500MB of available RAM, but it was a quick test to see if there was much of a difference.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.