2

How can I sort into MySQL in AlphaNumeric order;

Suppose I have a table with data like this.

Name   +   ID
AA     |   10
AE     |   2
AD     |   1

When I sort column ID the result is

1 
10
2

But when I add + 0 in select statement like this for column id

SELECT * FROM tableName ORDER BY columnName + 0;

1
2
10

But the result for column Name is this

AA
AE
AD

I already refer to this link but it doesn't work for me. Alphanumeric Order By in Mysql

Note: All column type is varchar and I cannot predict what data will be inserted. I know my problem is simple but still I can't get the result I want. Also I cannot use Java to sort the result because I use LIMIT on it. I'll appreciate any help.Thanks

Additional Info: (Sample Table to be sorted base on Name and ID) The header1 is the Name which set to another table and same with header2 w/c is ID

CREATE TABLE IF NOT EXISTS `sort` (
  `header1` varchar(200) DEFAULT NULL,
  `header2` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sort` (`header1`, `header2`) VALUES
('AA', '10'),
('AE', '2'),
('AD', '1'),
('AD', '1'),
('AF', 'a'),
('AF', 'a1'),
('1', 'a1');
Community
  • 1
  • 1
ace
  • 6,775
  • 7
  • 38
  • 47
  • 1
    When you can't use Java, then you should remove the tag. You should be also more precise, e.g., tell us what data you expect there. What about `(AA, 5, A6, B4, 9C)`, do you need to sort it too and how? – maaartinus Feb 03 '11 at 13:24
  • strings sort differently than numbers. you want to sort your string column like a number. if you store only numbers in that column then you should make it a numeric data type, and your problems will go away. – KM. Feb 03 '11 at 13:35
  • I can't use Java because I need to get the result by LIMIT. This is due to my pagination coding. I used LIMIT to speed up my query. And also I need to sort base on column. Yes I need to sort like that (AA,F,A6,B4,9C) – ace Feb 03 '11 at 13:38

2 Answers2

1

Change your field's datatype to a numerical data type instead of a Character type.

The plus zero must work because it is converting the data to a number.

jzd
  • 23,473
  • 9
  • 54
  • 76
  • I cannot specify it to numerical type because the user will be the one who will make the fields. So I decide to use varchar so neither letters or numeric is can be inserted to that particular column. – ace Feb 03 '11 at 13:34
  • Then why doesn't the second answer from "alphanumeric-order-by-in-mysql" not work? – jzd Feb 03 '11 at 13:38
  • I'm not sure why the results showed like that., It seems like its not sorting because it shows the order how I inserted the data. – ace Feb 03 '11 at 13:54
  • Did you insert the data in the order you wanted to sort in? Try inserting the data in a different order. – jzd Feb 03 '11 at 13:56
  • As default the data will be sorted base on the auto_incremented id, then next will be sorted according to the columns clicked by user., the data will be inserted from file upload or by individual insert using forms. Note I edited my post to add info on sample table. One of my solution will be add a data type for each columnName then use columnName + 0 if Numeric else just columnName, but I need to do a major changes or update in my code unfortunately I don't have time to that for now. So I hope there is an easy way to do this. – ace Feb 03 '11 at 14:40
0

It seems like its not sorting because it shows the order how I inserted the data.

Now it's clear! The problem is your misunderstanding how tables work. There's no defined order for a table. That's it. Whenever you do a SELECT without an ORDER BY, the DB is free to return the columns in any order it likes. And there's nothing you could do about it.

Normally, the rows get returned in an order determined by the primary key. Tf there's none, the insertion order gets used, unless there are deletions. After deletions, the DB puts the new records somewhere to fill the gaps. However, this is no guaranteed behavior, when you switch to another version or whatever, it may change.

Never rely on the order of rows in a table. Always use ORDER BY or a VIEW containing ORDER BY if the order matters.

maaartinus
  • 44,714
  • 32
  • 161
  • 320
  • Thanks for the info, that was very informative. But I'm using an ORDER BY and my table has a primary key. I think I need to do another way of getting the results. Actually I need to get results from DB then paginate it. I'm using LIMIT to speed up my query as a request from my Head. Then every time I click a page it will execute the query. And then the column must be sorted and that's my problem. I had no clue what type of data in that column so I assume it can be character a number or character + number. Is there a another way to sort it the way I want? – ace Feb 04 '11 at 02:30
  • You need to use `SORT BY` in the query itself. You can sort by any expression, maybe something like `SORT BY cast(col as SIGNED), col` could help (this uses the numerical value for numbers stored in char columns as the main criterion). – maaartinus Mar 04 '11 at 16:47