CODEWITHSUNDEEP
phpmysqlwordpresssql-order-by
Mr.M
Mr.M

Reputation: 135

Sort varchar integers first in mysql

I wrote this code to show datas from a wordpress database table in a html table:

global $wpdb;
$table_name = $wpdb->prefix . 'mytable';
$stats = $wpdb->get_results("SELECT * FROM $table_name ORDER BY cast(alexacr as UNSIGNED) ASC");
$itemnum = $wpdb->get_var("SELECT COUNT(id) FROM $table_name");
echo '<span style="font-family: sans-serif;position:relative;float: left;font-size: 18px;"><em>'.$itemnum.' Sites</em></span>';
echo '<table>';
echo '<thead>';
echo '<th>Nome &amp; Link</th><th>Alexa CR</th>';
echo '</thead>';
echo '<tbody>';
foreach ($stats as $stat){
echo'<tr>';
echo '<td><a title="'.str_replace('_','',$stat->name).'" href="'.$stat->url.'" target="_blank">'.str_replace('_','',$stat->name).'</a></td><td>'.$stat->alexacr.'</td>';
echo'</tr>';
 }
echo '</tbody>'; 
echo '</table>';

alexacr column is set to varchar. The result for alexacr column is like this:

N/A
1
2
3
4

How can i sort only numbers to be so?

1
2
3
4
N/A

Thanks.

Upvotes: 0

Views: 100

Answers (2)

Deepika Janiyani
Deepika Janiyani

Reputation: 1477

Try

  SELECT 
        *
  FROM 
        Table1
  Order By 
        CASE WHEN alexacr like 'N/A' then 1 else 0 end ASC,
        LPAD(alexacr, 20, '0') ASC

demo at http://sqlfiddle.com/#!2/210007/1

more details for LPAD at http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lpad

Upvotes: 1

Mihai
Mihai

Reputation: 26804

Try this:

ORDER BY alexacr ,alexacr *1

Edit

i guess n/a is a NULL so

ORDER BY CASE WHEN alexacr IS NULL THEN 1 ELSE 0 END,alexacr

SQL Fiddle

SQL Fiddle

Upvotes: 1

Related Questions