The data I select in MySQL is not in order when I use "order by"

Question

   SELECT candidate_num FROM candidate ORDER BY candidate_num

The results of the query above is:

We can see candidate_num is not in order. Following is the structure of this table. I cannot figure why the "2" is after "19".

CREATE TABLE `candidate` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `candidate_num` varchar(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `age` int(3) NOT NULL,
  `major` varchar(50) NOT NULL,
  `company` varchar(50) NOT NULL,
  `department` varchar(50) NOT NULL,
  `native_place` varchar(50) NOT NULL,
  `ethnicity` varchar(50) NOT NULL,
  `highest_education` varchar(50) NOT NULL,
  `group` varchar(50) DEFAULT NULL,
  `is_elected` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `candidate_num` (`candidate_num`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8;

score 4 · Answer 1 · answered May 22 '17 at 05:26

4

Here "candidate_num" is a string (varchar field), so it gets ordered lexically. If it is so, you can probably order it by:

SELECT `candidate_num` FROM `candidate` ORDER BY convert(`candidate_num`, decimal) ASC;

Note: For varchar it orders the data in lexical order, i.e. first it put the value having 1 in first place and then the value having 2 on second place and so on.

answered May 22 '17 at 05:26

Mayank Pandeyz

25,704
4
40
59

2

This is an hack which is not recommended to use. If the column will have only integers, convert the data type of column to numeric data type. If you cannot alter the column due to other reasons, you can also try `select candidate_num from (candidate) order by (candidate_num+0);` – hamnix May 22 '17 at 05:31

Hieu Le · Answer 2 · 2017-05-22T05:58:54.887

1

Data type of candidate_num is varchar type. You cannot order by values of this columns like a numeric types.

Solution:

 `candidate_num` int(6) NOT NULL,

Note:

SELECT `candidate_num` FROM `candidate` ORDER BY convert(`candidate_num`, decimal) ASC;

If you keep varchar datatype for this column and convert values when using order by, the performance will completly down

edited May 22 '17 at 05:58

answered May 22 '17 at 05:27

Hieu Le

1,042
8
18

@Strawberry, it's just an example – Hieu Le May 22 '17 at 07:29
A better example would omit it! – Strawberry May 22 '17 at 07:30

score 1 · Answer 3 · edited May 23 '17 at 11:47

Er, candidate number IS in order, it is in value number order rather than number quantity order. [as better described by Mayank; it is sorting in lexical order]

By Default in MySQL numeric columns are naturally sorted (the sorting you want), so simply change your ORDER BY column from VARCHAR to a numerical column using CAST(). Below is setting to an int column:

SELECT candidate_num FROM candidate ORDER BY CAST(candidate_num AS UNSIGNED)

see also:
- Natural Sort in MySQL
- Cast from VARCHAR to INT - MySQL

As others have said, if your column contents will always be numeric, you really should change the column to an INT, or DECIMAL, etc... numerical value, rather than trying to CAST the value on evey SQL call to it.

score 0 · Answer 4 · answered May 22 '17 at 05:28

If the candidate number will always remains as Number then you should have to convert the data type of candidate number As integer instead of keeping as Varchar.

Once you change the data type of candidate_number to integer you will get the the number in sorted order.

The data I select in MySQL is not in order when I use "order by"

4 Answers4