2

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

pilcrow
  • 56,591
  • 13
  • 94
  • 135
Avinash
  • 385
  • 3
  • 11
  • 26
  • Have you tried converting to float in order by? – kgu87 Nov 19 '13 at 18:34
  • Cast the numbers to a number value. This might fail, though, if you also got numbers like `1.2.1`. Anyway, I'd expect a field named `xyz_id` to contain an integer. – GolezTrol Nov 19 '13 at 18:34
  • Not easily. If you know the max length of each set is 2 characters long, you could add a zero in front of the ones that are only one space long when sorting. thus 1.1 becomes 01.01 so it will sort correctly. however, if you have a 1.101 then they would need to have 2 zero's in front. – xQbert Nov 19 '13 at 18:35
  • Each part can be maximum 2 characters length. So u are right, putting 0 in beginning will help if some part has 1 character but I was wondering if it can be done without that. – Avinash Nov 19 '13 at 18:38
  • possible duplicate of [mysql sorting of version numbers](http://stackoverflow.com/questions/7508313/mysql-sorting-of-version-numbers) – pilcrow Nov 19 '13 at 19:35
  • Are you ever going to have multidigit terms in there, e.g. `1.10.5.99`? if so, there is very little you can do to sort this without incredibly ugly queries. – Marc B Nov 19 '13 at 19:38
  • Possible duplicate of: [http://stackoverflow.com/questions/13934418/mysql-order-string-as-number][1] [1]: http://stackoverflow.com/questions/13934418/mysql-order-string-as-number – Noam Rathaus Nov 19 '13 at 19:56
  • I think I have a fairly clean solution for this that doesn't require any change in the way you store the data, but it depends on knowing the maximum number of "dots" that might ever be seen... is there a practical "max" in your application? – Michael - sqlbot Nov 20 '13 at 13:55

2 Answers2

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

Mihai
  • 26,325
  • 7
  • 66
  • 81
-2

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

Mats Kindahl
  • 1,863
  • 14
  • 25