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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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);
SELECT 'something' + 0;
share|improve this answer
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

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

select _tid,_name from teacher order by _tid + 0


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`)

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

I have used dezso's padding method in the past, but not too long ago I found a simple solution, which was to sort the field first using its length, then its value:

SELECT _tid,_name FROM teacher ORDER BY length(_tid), _tid;

which causes single digit values to sort before double digit values, and double digit to sort before 3-digit, and so on. I have not bench-marked the performance however.

share|improve this answer

Your Answer


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.