Datatables: Sort By Numeric Data-order Not Working?

I am using DataTables version 1.10. I want to make a column sortable by a numeric value, when the value shown in the column is not numeric. I can see that what I need to do is add

Solution 1:

SOLUTION

You can use orthogonal data which is the term jQuery DataTables uses for a way of providing different data for display, sort and filter operations.

Also you need to explicitly state column data type with type: "num".

$.each(data, function (i, d) {
    d.won_percent = {
        sort: (d.won / d.played) * 100
    };
    d.won_percent.display = 
        d.won + '/' + d.played +
        ' (' + Math.round(d.won_percent.sort * 10) / 10 + '%)';
});

// ... skipped ...

{
    "data": "won_percent",
    "title": "Games won",
    "type": "num",
    "render": {
        "_": "display",
        "sort": "sort"
    }
}, 

DEMO

See updated jsFiddle for code and demonstration.

LINKS

• Orthogonal data example

Solution 2:

here is a native way of doing it

add this "columnDefs" object inside the datatables settings object

columnDefs: [
  { 
     targets: [1], // cell targetrender: function(data, type, full, meta) {
             if(type === "sort") {
                var api = new $.fn.dataTable.Api(meta.settings);
                var td = api.cell({row: meta.row, column: meta.col}).node(); // the td of the row
                data = $(td).attr('data-order'); // the data it should be sorted by
             }
             return data;
         }
     },
  ],

here is a working fiddle : http://jsfiddle.net/07nk5wob/6/

Solution 3:

Working fiddle here: http://jsfiddle.net/annoyingmouse/07nk5wob/9/

Basically you need a special sorting thingie:

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
   "special-pre": function ( a ) {
       var regExp = /\(([^)]+)\)/;
       var matches = regExp.exec(a);
       returnparseFloat(matches[1]);
   },
   "special-asc": function ( a, b ) {
       return ((a < b) ? -1 : ((a > b) ? 1 : 0));
   },
   "special-desc": function ( a, b ) {
       return ((a < b) ? 1 : ((a > b) ? -1 : 0));
   }
}); 

Solution 4:

Here is a working fiddle for your problem.

You can implement your own sorting plugin to do that. It is quite simple. Here I have extend the DataTable plugin by adding a percentage sorting method. You can see in the percentage-pre method, I'm only returning the numeric value which you want to sort.

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
   "percentage-pre": function ( a ) {
       var regExp = /\(([^)]+)\)/;
       var matches = regExp.exec(a);
       var exactVal = matches[1];
       returnparseFloat(exactVal.slice(0, -1));
   },
   "percentage-asc": function ( a, b ) {
       return ((a < b) ? -1 : ((a > b) ? 1 : 0));
   },
   "percentage-desc": function ( a, b ) {
       return ((a < b) ? 1 : ((a > b) ? -1 : 0));
   }
});

Then in the above fiddle you can see, I am adding below code in DataTable init method:

columnDefs: [
   { type: 'percentage', targets: 1 }
 ] 

Hope this is what you need!