Sorting by a comma separated list, treating as integer
<p>I have a query that gets a sort order through a group concat. It returns the sorting order in a comma separated list that has everything perfectly set. Ordering by the comma separated list ALMOST works perfectly. However it treats each set of data in the comma separated list as if it were a char.</p>

<p>That means when sorting it would go like this:</p>

<pre><code>1,0
10,0
11,0
12,0
13,0
14,0
15,0
16,0
17,0
18,0
19,0
2,0
21,0
3,0
4,0
5,0
6,0
7,0
8,0
9,0
</code></pre>

<p>Rather than the correct order:</p>

<pre><code>1,0
2,0
3,0
4,0
5,0
6,0
7,0
8,0
9,0
10,0
11,0
...    
20,0
21,0
</code></pre>

<p>How do I force mysql to treat these as integers? It would need to sort things correctly for when there are multiple values that are very similar. For example:</p>

<pre><code>0,0
0,1
0,2
1,0
2,0
2,1
3,5
4,2
etc
</code></pre>

<p>The first value always sets the first position, then the second value the next position and so on. </p>

<p>The list can vary between 1 single value and 2-3 or more... but it is always filled with numbers. I've looked at substring_index, temporary tables, casting.. etc. Nothing seems to be working. Its weird because otherwise mySQL can sort just fine.</p>