order null values ascending with caml query

Question

I have a customOrder column in list, its ordered by that number ascending.

I want all null values listed after items that has value in customorder. I wish all null values would be ordered asc as default but not So how can I do that with caml query is it possible?

as you see below all items null values listed top of the table I want them come after items that have value in customorder

Answer 1

Unfortunately this is the default OOTB behaviour of CAML query using SharePoint.

To ignore get null values at the end, you should create a calculated column in the list which will calculate if the sort column is empty or not. The formula of that would be =IF(ISBLANK([CustomOrder]),0,1) . After that, you should sort CAML by these 2 columns as below:

<OrderBy>
   <FieldRef Name='CalculatedColumnInternalName' Ascending='False' />
   <FieldRef Name='CustomOrder' Ascending='False' />
</OrderBy>

To create calculated column - follow this link Create calculated column and use the formula I mentioned.

Reference - Ignore Null Values in OrderBy Asc While creating CAML Query