In a MySQL database, I have 4 tables, each of which has a field named content_id which is defined as varchar. The values of content_id in Table 1 are 1 2 etc.
The values of content_id in Table 2 are 1.1 1.2 etc.
The values of content_id in Table 3 are 1.1.2 etc.
The values of content_id in Table 4 are 1.1.1.1 1.1.1.2 etc.
I have written SELECT query in php to read records of these tables. I have sorted the records by using "order by content_id" It works fine for Table 1. However, for table 2, I am getting 1.10 before 1.2. Similar problems for Tables 3 and 4.
I know why it is happening - this is because alphabeically 1.10 comes before 1.2
But, is there any way I can sort as 1.1, 1.2, 1.3 ... 1.10, 1.11 etc. ?
Thank you
You can order a column by an expression, so
SELECT * FROM tbl2 ORDER BY CAST(content_id AS DECIMAL(5,2));
See http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast
SELECT *
FROM table1
ORDER BY 1*SUBSTRING_INDEX(content_id, '.', 1) ASC,
1*SUBSTRING_INDEX(content_id, '.', -1) ASC
It orders first by the numbers left of the dot and then by those on the right of it.It essentially splits the decimal.