How to sort on a numeric column in jQuery DataTables

Question

(Looked at a bunch of answers on this topic, none of which applies to this question.)

The DataTables have the feature of letting the user click on each column's Up/Down triangle icons to sort in Ascending or Descending order. I have loaded the data as follows

        oTable.fnAddData( ["Bogus data","1,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","3,541,512","2.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","541,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","2,541,512","32.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","741,512","3.5%","0","0","0"]);
        oTable.fnAddData( ["A Bogus data","41,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","2,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Z Bogus data","1,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","3,541,512","2.5%","0","0","0"]);
        oTable.fnAddData( ["La Bogus data","541,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["The Bogus data","2,541,512","32.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","741,512","3.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","41,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","2,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","1,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","3,541,512","2.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","541,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","2,541,512","32.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","741,512","3.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","41,512","1.5%","0","0","0"]);

In column number 2 the numeric values are processed alphabetically when I click on the Up/Down triangles. How can I adjust it so that the second column Up/Down arrows will trigger the proper way, treating the characters as numbers. I tried using the following initialization:

oTable = $('.utable').dataTable( {
"aoColumns": [{ sWidth: '60%' },{sWidth: '30%', "sType": "numeric"},{ sWidth: '10%' }],
"sDom": 'rt',
"sScrollY":"200px",
"bPaginate":false,
"bFilter":false,
"bInfo": false});

All this does is to lock the colum and the Up/Down icons will not work in the header of that column.

Pete · Accepted Answer · 2013-03-06 21:57:47Z

4

Your problem is that the numbers are not being recognized as such and even if they were, the commas in your numbers would probably the sorting function off (because it will probably not properly remove them).

One option you have is to implement your own sorting function which deals with your numbers properly. Here's an example that does what you need:

http://live.datatables.net/oborug/2/edit

PS -- Here's the relevant documentation: http://datatables.net/development/sorting

answered Mar 6, 2013 at 21:57

Pete

2,55815 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DKean Over a year ago

Pete, you rascal. You understood the problem perfectly! You rock, dude!! Thank you... It worked right off the bat! This remains for me to comb carefully.

DKean Over a year ago

Can't thank you enough, dude. I have been busting my head for a couple days on and off without success. This solution is adroit as an answer could be!

Nathan Over a year ago

This looks great - but I must be missing something, maybe just looking at it wrong - when I go to that example from the posted link it behaves exactly as advertised, but I can't for the life of me see the custom sorting code, or how it may have been included - is this functionality already in the nightly build perhaps? Or am I looking in the wrong place?

Michal · Accepted Answer · 2013-03-06 21:55:22Z

2

Technically column number two contains strings (1,000) - numbers with a comma and so does column 3 - numbers with percentages). Best thing for you to do is to pass the data to data tables as an integer (without commas and %) and write a custom formatter to add commas and percentages using mRender option (read about it at http://www.datatables.net/usage/columns).

If you add custom formatting to your data also do not forget to set the option to use underlying data as your sort source rather than the displayed data.

answered Mar 6, 2013 at 21:55

Michal

13.7k4 gold badges38 silver badges33 bronze badges

5 Comments

DKean Over a year ago

You said, "...do not forget to set the option to use underlying data as your sort source rather than the displayed data." How do I make sure of that?

Michal Over a year ago

In the older version of datatables you would use bUseRendered=false but that option is now deprecated - have a good read of mData/mRender docs for an alternative. But ultimately it will be some option that you must set

DKean Over a year ago

Reading furiously... err earnestly...!

DKean Over a year ago

There is too little information on how to use mRender. The two examples given are using Ajax and I cannot weed through it. And the author of the Datatables simply did not document that option carefully enough.

Lyman Zerga Over a year ago

I have the exact same problem as DKean. I don't have much knowledge about JavaScript. Can someone please post the solution ?

Oussaki · Accepted Answer · 2013-03-06 21:31:18Z

0

you have to define a function of sorting ( try to use sort by selection or sort by insertion ) try to stock your data variables in an array and do the caculations , else you can do the sort directly with your data

answered Mar 6, 2013 at 21:31

Oussaki

1,6972 gold badges24 silver badges35 bronze badges

1 Comment

DKean Over a year ago

What I need to do is find where their sorting function lies and replace it. Can you help me find it, else the sorting will not be dynamic. The user needs to have the power to sort by every column just the way it is set right now. I cannot lose the functionality of the little triangles in the header columns

Pranav Singh · Accepted Answer · 2014-01-03 03:07:36Z

Hello I made this using parseFloat and replace methods and using this example http://datatables.net/release-datatables/examples/basic_init/multi_col_sort.html

jQuery.fn.dataTableExt.oSort["string-nbr-asc"]  = function(x,y) {return ((parseFloat(x.replace(",","")) < parseFloat(y.replace(",",""))) ? -1 : ((parseFloat(x.replace(",","")) > parseFloat(y.replace(",",""))) ?  1 : 0));};

jQuery.fn.dataTableExt.oSort["string-nbr-desc"] = function(x,y) {return ((parseFloat(x.replace(",","")) < parseFloat(y.replace(",",""))) ?  1 : ((parseFloat(x.replace(",","")) > parseFloat(y.replace(",",""))) ? -1 : 0));};

If you have 10 columns and want to sort 7,8,9 wich are numbers like 7,081 1,925.49 use

"aoColumns":[null,null,null,null,null,null,{ "sType": "string-nbr" },{ "sType": "string-nbr" },{ "sType": "string-nbr" },null]

Collectives™ on Stack Overflow

How to sort on a numeric column in jQuery DataTables

4 Answers 4

3 Comments

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related