MySQL query to display ASC order in number column?

You can achieve this with the help of CAST() function. Let us first create a table −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">DemoTable</span>
<span class="pun">   (</span>
<span class="typ">   Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> NOT NULL AUTO_INCREMENT PRIMARY KEY</span><span class="pun">,</span>
<span class="typ">   Score</span><span class="pln"> </span><span class="kwd">int</span>
<span class="pun">   );</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.72</span><span class="pln"> sec</span><span class="pun">)</span>

Insert some records in the table using insert command −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">Score</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">10</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.19</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">Score</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">100</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">Score</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">11</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.13</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">Score</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">97</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">Score</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">78</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.13</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">Score</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">89</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.18</span><span class="pln"> sec</span><span class="pun">)</span>

Display all records from the table using select statement −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">;</span>

This will produce the following output −

+----+-------+
| Id | Score |
+----+-------+
| 1  | 10    |
| 2  | 100   |
| 3  | 11    |
| 4  | 97    |
| 5  | 78    |
| 6  | 89    |
+----+-------+
6 rows in set (0.00 sec)

Following is the query to set ASC order in number column −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable </span><span class="pln">order </span><span class="kwd">by</span><span class="pln"> cast</span><span class="pun">(</span><span class="typ">Score</span><span class="pln"> AS SIGNED INTEGER</span><span class="pun">)</span><span class="pln"> ASC</span><span class="pun">;</span>

This will produce the following output −

+----+-------+
| Id | Score |
+----+-------+
| 1  | 10    |
| 3  | 11    |
| 5  | 78    |
| 6  | 89    |
| 4  | 97    |
| 2  | 100   |
+----+-------+
6 rows in set (0.00 sec)