How to sort drop down list numerically in DataTables

Question

I have this code. What it does is simply displaying with DataTables and put the drop down selection filter for every column at the bottom.

var dataSet = [
  [1,"Airi Satou", "Accountant", "Tokyo", "5407", "2008/11/28", "$162,700"],
  [2,"Angelica Ramos", "Chief Executive Officer (CEO)", "London", "5797", "2009/10/09", "$1,200,000"],
  [10,"Gavin Joyce", "Developer", "Edinburgh", "8822", "2010/12/22", "$92,575"],
  [3,"Jennifer Chang", "Regional Director", "Singapore", "9239", "2010/11/14", "$357,650"],
  [5,"Brenden Wagner", "Software Engineer", "San Francisco", "1314", "2011/06/07", "$206,850"],
  [9,"Fiona Green", "Chief Operating Officer (COO)", "San Francisco", "2947", "2010/03/11", "$850,000"],
  [12,"Shou Itou", "Regional Marketing", "Tokyo", "8899", "2011/08/14", "$163,000"],
  [23,"Martena Mccray", "Post-Sales support", "Edinburgh", "8240", "2011/03/09", "$324,050"],
  [99,"Unity Butler", "Marketing Designer", "San Francisco", "5384", "2009/12/09", "$85,675"]
];

 $(document).ready(function() {

var columns = [
    {title: "ID"},
    {title: "Name"}, 
    {title: "Position"}, 
    {title: "Office"}, 
    {title: "Extn."}, 
    {title: "Start date"}, 
    {title: "Salary"}
];

// Footer construction
var $tfoot = $("#example tfoot tr");
for (var i = 0, len = columns.length; i < len ; i++){
    $tfoot.append("<th>");
}

$('#example').DataTable( {
    data: dataSet,
    columns: columns,
    initComplete: function (setting, json) {
        this.api().columns().every( function () {
            var column = this;
            var select = $('<select><option value=""></option></select>')
                .appendTo( $(column.footer()).empty() )
                .on( 'change', function () {
                    var val = $.fn.dataTable.util.escapeRegex(
                        $(this).val()
                    );
 
                    column
                        .search( val ? '^'+val+'$' : '', true, false )
                        .draw();
                } );
 
            column.data().unique().sort().each( function ( d, j ) {
                select.append( '<option value="'+d+'">'+d+'</option>' )
            } );
        } );
    } 
    
} );
} );

<link href="https://cdn.datatables.net/r/dt/jq-2.1.4,dt-1.10.9,b-1.0.3,b-flash-1.0.3/datatables.min.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/r/dt/jq-2.1.4,dt-1.10.9,b-1.0.3,b-flash-1.0.3/datatables.min.js"></script>

<table id="example" class="display" width="100%">
<tfoot><tr></tr></tfoot>
</table>

As you can see after running the snippet, the first ID column is not sorted numerically. Here is the screenshot:

How can enable that? This is different with other question because it's specific to DataTables.

possible duplicate of [How to sort an array of integers correctly](http://stackoverflow.com/questions/1063007/how-to-sort-an-array-of-integers-correctly) — Hacketo, Sep 10 '15 at 11:57
@Hacketo, this is not a duplicate question because array may contain numbers or strings which may not known initially. — Gyrocode.com, Sep 10 '15 at 11:59
@Gyrocode.com then the first answer does not work with strings values `"111" < "12"` BTW OP state for number. So yes it's a duplicate — Hacketo, Sep 10 '15 at 12:02
@Gyrocode.com this case is not specific to anything, only sorting an array of data. OP might have remove all not necessary code but he is not able to say what this code is doing. I'm working on this code since yesterday and at the start the code was taken from an example to datatable. OP do not understand a piece of JS — Hacketo, Sep 10 '15 at 12:19
@Hacketo, [example on DataTables site](http://datatables.net/examples/api/multi_filter_select.html) is not correct because of the problem the OP found with column containing numerical data. Example on DataTables site works only because the numbers all double-digit which is deceptive. — Gyrocode.com, Sep 10 '15 at 12:21
@Hacketo, the problem is shown in the question were numbers are sorted as strings without an argument to `sort`. However other columns may need to be sorted as strings or using custom sorting functions if necessary. — Gyrocode.com, Sep 10 '15 at 12:27

score 7 · Accepted Answer · edited Jun 20 '20 at 09:12

SOLUTION

Column data type is automatically determined by jQuery DataTables during initialization, see data types supported by default.

In initComplete function you can temporarily sort each column in ascending order and then restore the initial order once all drop-down boxes are added, see the code below.

initComplete: function (settings, json) {
    // Get initial order
    var orderInit = this.api().order();
    
    this.api().columns().every( function (index) {
        var column = this;
        var select = $('<select><option value=""></option></select>')
            .appendTo( $(column.footer()).empty() )
            .on( 'change', function () {
                var val = $.fn.dataTable.util.escapeRegex(
                    $(this).val()
                );

                column
                    .search( val ? '^'+val+'$' : '', true, false )
                    .draw();
            } );

        // NOTE: Temporarily sort the column data before retrieving it
        // with data() function.
        column.order('asc').draw(false).data().unique().each( function ( d, j ) {
            select.append( '<option value="'+d+'">'+d+'</option>' )
        } );
    } );
  
    // Restore initial order
    this.api().order(orderInit).draw(false);
}

DEMO

var dataSet = [
  [1,"Airi Satou", "Accountant", "Tokyo", "5407", "2008/11/28", "$162,700"],
  [2,"Angelica Ramos", "Chief Executive Officer (CEO)", "London", "5797", "2009/10/09", "$1,200,000"],
  [10,"Gavin Joyce", "Developer", "Edinburgh", "8822", "2010/12/22", "$92,575"],
  [3,"Jennifer Chang", "Regional Director", "Singapore", "9239", "2010/11/14", "$357,650"],
  [5,"Brenden Wagner", "Software Engineer", "San Francisco", "1314", "2011/06/07", "$206,850"],
  [9,"Fiona Green", "Chief Operating Officer (COO)", "San Francisco", "2947", "2010/03/11", "$850,000"],
  [12,"Shou Itou", "Regional Marketing", "Tokyo", "8899", "2011/08/14", "$163,000"],
  [23,"Martena Mccray", "Post-Sales support", "Edinburgh", "8240", "2011/03/09", "$324,050"],
  [99,"Unity Butler", "Marketing Designer", "San Francisco", "5384", "2009/12/09", "$85,675"]
];

$(document).ready(function() {

var columns = [
    {title: "ID"},
    {title: "Name"}, 
    {title: "Position"}, 
    {title: "Office"}, 
    {title: "Extn."}, 
    {title: "Start date"}, 
    {title: "Salary"}
];

// Footer construction
var $tfoot = $("#example tfoot tr");
for (var i = 0, len = columns.length; i < len ; i++){
    $tfoot.append("<th>");
}

$('#example').DataTable( {
    data: dataSet,
    columns: columns,
    initComplete: function (settings, json) {
        // Get initial order
        var orderInit = this.api().order();
        
        this.api().columns().every( function (index) {
            var column = this;
            var select = $('<select><option value=""></option></select>')
                .appendTo( $(column.footer()).empty() )
                .on( 'change', function () {
                    var val = $.fn.dataTable.util.escapeRegex(
                        $(this).val()
                    );
 
                    column
                        .search( val ? '^'+val+'$' : '', true, false )
                        .draw();
                } );
 
            column.order('asc').draw(false).data().unique().each( function ( d, j ) {
                select.append( '<option value="'+d+'">'+d+'</option>' )
            } );
        } );
      
        // Restore initial order
        this.api().order(orderInit).draw(false);
    } 
    
} );
} );

<link href="https://cdn.datatables.net/r/dt/jq-2.1.4,dt-1.10.9,b-1.0.3,b-flash-1.0.3/datatables.min.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/r/dt/jq-2.1.4,dt-1.10.9,b-1.0.3,b-flash-1.0.3/datatables.min.js"></script>

<table id="example" class="display" width="100%">
<tfoot><tr></tr></tfoot>
</table>

NOTES

See issue #661 on GitHub for more details and further discussion.

Great answer, but please specify in the solution section about the additional logic for the **Salary** column. — Yurii Vykaliuk, Sep 10 '15 at 13:15
@neversaint, added much better solution that uses DataTables internal sorting functions and doesn't require custom functions. — Gyrocode.com, Sep 10 '15 at 13:45
@Gyrocode.com: You're a life saver. Thanks also for answering my 'moving buttons' question. — neversaint, Sep 10 '15 at 14:10

score 5 · Answer 2 · edited Sep 10 '15 at 11:54

CAUSE

By default, array sorting method without the function that defines the sorting order, will sort your array as a string so result that you have is expected. You can read here for more info - Array.prototype.sort(). So you need to add a function that will sort your data properly for both numbers and strings.

SOLUTION

Add sortFunction function and use it as an argument for sort() function as shown below:

var sortFunction = function(a, b) {
    if(a < b) return -1;
    if(a > b) return 1;
    return 0;
};

column.data().unique().sort(sortFunction).each( function ( d, j ) {

DEMO

var dataSet = [
  [1,"Airi Satou", "Accountant", "Tokyo", "5407", "2008/11/28", "$162,700"],
  [2,"Angelica Ramos", "Chief Executive Officer (CEO)", "London", "5797", "2009/10/09", "$1,200,000"],
  [10,"Gavin Joyce", "Developer", "Edinburgh", "8822", "2010/12/22", "$92,575"],
  [3,"Jennifer Chang", "Regional Director", "Singapore", "9239", "2010/11/14", "$357,650"],
  [5,"Brenden Wagner", "Software Engineer", "San Francisco", "1314", "2011/06/07", "$206,850"],
  [9,"Fiona Green", "Chief Operating Officer (COO)", "San Francisco", "2947", "2010/03/11", "$850,000"],
  [12,"Shou Itou", "Regional Marketing", "Tokyo", "8899", "2011/08/14", "$163,000"],
  [23,"Martena Mccray", "Post-Sales support", "Edinburgh", "8240", "2011/03/09", "$324,050"],
  [99,"Unity Butler", "Marketing Designer", "San Francisco", "5384", "2009/12/09", "$85,675"]
];

 $(document).ready(function() {

var columns = [
    {title: "ID"},
    {title: "Name"}, 
    {title: "Position"}, 
    {title: "Office"}, 
    {title: "Extn."}, 
    {title: "Start date"}, 
    {title: "Salary"}
];

// Footer construction
var $tfoot = $("#example tfoot tr");
for (var i = 0, len = columns.length; i < len ; i++){
    $tfoot.append("<th>");
}

$('#example').DataTable( {
    data: dataSet,
    columns: columns,
    initComplete: function (setting, json) {
        this.api().columns().every( function () {
            var column = this;
            var select = $('<select><option value=""></option></select>')
                .appendTo( $(column.footer()).empty() )
                .on( 'change', function () {
                    var val = $.fn.dataTable.util.escapeRegex(
                        $(this).val()
                    );
 
                    column
                        .search( val ? '^'+val+'$' : '', true, false )
                        .draw();
                } );
 
                var sortFunction = function(a, b) {
                    if(a < b) return -1;
                    if(a > b) return 1;
                    return 0;
                };
                column.data().unique().sort(sortFunction).each( function ( d, j ) {
                select.append( '<option value="'+d+'">'+d+'</option>' )
            } );
        } );
    } 
    
} );
} );

<link href="https://cdn.datatables.net/r/dt/jq-2.1.4,dt-1.10.9,b-1.0.3,b-flash-1.0.3/datatables.min.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/r/dt/jq-2.1.4,dt-1.10.9,b-1.0.3,b-flash-1.0.3/datatables.min.js"></script>

<table id="example" class="display" width="100%">
<tfoot><tr></tr></tfoot>
</table>

Great answer, but If you remove `sort()`, other select boxes will have unsorted data. It's not a complete solution. — Gyrocode.com, Sep 10 '15 at 11:08
only need to do `return a - b` your code does not handle string values `"111" < "12"` return true — Hacketo, Sep 10 '15 at 12:02
@Hacketo - are you sure? `return a - b ` will work good only for numbers but we are comparing both numbers and strings. So we need to refactor the whole code - first verify if values in the column is numbers or strings and then use appropriate sorting function. — Yurii Vykaliuk, Sep 10 '15 at 12:19
The **Salary** column looks like a column with a numbers but in real it is treated as a `string`, because it values have '$' and ',' symbols. So, additional function should be written for this column. For example, the function can clone existing array, modify array of values to array of objects, where each object will have two properties - primary value and modified value - the real number without '$' and ','. Then sort by real numbers, remove them and return correct sorted array of primary values. — Yurii Vykaliuk, Sep 10 '15 at 13:09

w3spi · Answer 3 · 2015-09-10T11:35:21.503

1

It isn't necessary to have the sort() method.

So, just change it :

column.data().unique().each( function ( d, j ) {
    select.append( '<option value="'+d+'">'+d+'</option>' )
});

Please find the JSFIDDLE

Edit : add unique() (I had remove in error) + update jsfiddle

edited Sep 10 '15 at 11:35

answered Sep 10 '15 at 10:30

w3spi

4,380
9
47
80

Thank you @Gyrocode.com ! I,had remove with error. Will change fiddle asap i will be on computer – w3spi Sep 10 '15 at 11:00
1

Great answer, but If you remove `sort()`, other select boxes will have unsorted data. It's not a complete solution. – Gyrocode.com Sep 10 '15 at 11:09
1

@Gyrocode.com: Thanks. You're right other column need to be properly sorted. For example 2nd or 3rd column may also contain numeric values. How would you approach that? – neversaint Sep 10 '15 at 11:13

How to sort drop down list numerically in DataTables

3 Answers3

SOLUTION

DEMO

NOTES