CODEWITHSUNDEEP
phpmysqlsqlsorting
Avinash
Avinash

Reputation: 415

MySQL select SQL: how to write order by to short 1.1.1, 1.1.2 etc

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

Upvotes: 2

Views: 3243

Answers (2)

Mihai
Mihai

Reputation: 26784

SELECT *
FROM table1
ORDER BY 1*SUBSTRING_INDEX(content_id, '.', 1) ASC,                      
         1*SUBSTRING_INDEX(content_id, '.', -1) ASC

Sql fiddle

It orders first by the numbers left of the dot and then by those on the right of it.It essentially splits the decimal.

Upvotes: 1

Mats Kindahl
Mats Kindahl

Reputation: 2060

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

Upvotes: -2

Related Questions